Querying on one to many relationship does not include parent table column name in the query generated through JPA

Question

My application is built using the following

• Spring Boot
• JPA
• Crnk (https://www.crnk.io/releases/stable/documentation/)
• MariaDB
• Flyway

I have a table Incentive Coupon which has One-to-Many relationship to baskets. So i have designed one to many relationship as below.

IncentiveCoupon table is as follows

    @SuppressWarnings("ALL")
    @Entity
    @Getter
    @Setter
    @NoArgsConstructor
    @Table(name = "incentive_coupons")
    @ToString
    public class IncentiveCoupon {
@Id
    @Column(columnDefinition = "BINARY(16)")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private UUID id;

    @Column(name = "created_at", columnDefinition = "TIMESTAMP")
    @CreationTimestamp
    private ZonedDateTime createdAt;

    @Column(name = "updated_at", columnDefinition = "TIMESTAMP")
    @UpdateTimestamp
    private ZonedDateTime updatedAt;

    @Column(name = "characteristics", columnDefinition = "TEXT")
    @Convert(converter = MapConverter.class)
    private Map<String, String> characteristics;
    
    @OneToMany(fetch = FetchType.EAGER, cascade = {CascadeType.MERGE, CascadeType.PERSIST}, mappedBy = "coupon", orphanRemoval = true)
        //@JoinColumn(name = "incentive_coupon_id")
        private Set<IncentiveCouponBaskets> baskets = new HashSet<IncentiveCouponBaskets>();

And the CrnkModel of IncentiveCoupon is defined as below

@JsonApiResource(type = "incentive-coupons", resourcePath = "marketing/incentive-coupons")
@Getter
@Setter
@NoArgsConstructor
public class IncentiveCouponCrnkModel  extends BaseCrnkModel {

    @JsonIgnore
    private List<UUID> basketIds;

    @JsonApiField(patchable = true)
    @JsonApiRelation(serialize = SerializeType.ONLY_ID, lookUp = LookupIncludeBehavior.AUTOMATICALLY_WHEN_NULL, idField = "basketIds", mappedBy = "coupon")
    @JsonProperty("baskets")
    private List<IncentiveCouponBasketCrnkModel> relatedBaskets;

The IncentiveCouponBaskets table is defined as follows

@Entity
@Table (name = "incentive_coupon_baskets")
@Getter
@Setter
public class IncentiveCouponBaskets {

    @Id
    @Column(columnDefinition = "BINARY(16)")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private UUID id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "incentive_coupon_id", referencedColumnName = "id")
    private IncentiveCoupon coupon;

    @Column(name = "basket_id", columnDefinition = "BINARY(16)")
    private UUID basketId;

    @Column(name = "created_at", columnDefinition = "TIMESTAMP")
    @CreationTimestamp
    private ZonedDateTime createdAt;

    @Column(name = "updated_at", columnDefinition = "TIMESTAMP")
    @UpdateTimestamp
    private ZonedDateTime updatedAt;
}

And its CrnkModel is defined as follows

@JsonApiResource(type = "incentive-coupon-baskets")
@Getter
@Setter
@NoArgsConstructor
public class IncentiveCouponBasketCrnkModel extends BaseCrnkModel {

    @JsonApiId
    private UUID id;

    @JsonIgnore
    private UUID couponId;

    @JsonApiField(patchable = false)
    @JsonApiRelation(serialize = SerializeType.ONLY_ID, lookUp = LookupIncludeBehavior.AUTOMATICALLY_WHEN_NULL, idField = "couponId")
    @JsonProperty("coupon")
    private IncentiveCouponCrnkModel coupon;

    @JsonIgnore
    private UUID basketId;

    @JsonApiField(patchable = false)
    @JsonApiRelation(serialize = SerializeType.ONLY_ID, lookUp = LookupIncludeBehavior.AUTOMATICALLY_WHEN_NULL, idField = "basketId")
    @JsonProperty("baskets")
    private BSSBasketCrnkModel relatedBaskets;

And the BssBasketCrnkModel is as follows

@Getter
@Setter
@JsonApiResource(type = "baskets")
public class BSSBasketCrnkModel {
    @JsonApiId
    private UUID id;

And in my IncentiveCouponCrnkRepository i have one of the method as follows

@Component
@Slf4j
public class IncentiveCouponCrnkRepository extends BaseResourceRepository<IncentiveCouponCrnkModel, UUID> {

@Override
    public ResourceList<IncentiveCouponCrnkModel> findAll(QuerySpec querySpec) {
        return querySpec.apply(repository.findAll(IncentiveSearchBuilder.findIncentiveCoupon(querySpec, getBrand())).stream()
                .map(incentiveCoupon -> mapper.transform(incentiveCoupon))
                .collect(Collectors.toList()));
    }

And in IncentiveSearchBuilder the method findIncentiveCoupon is defined as follows

   public class IncentiveSearchBuilder {
    
        public static Specification<IncentiveCoupon> findIncentiveCoupon(QuerySpec querySpec, String brand) {
    return (root, query, builder) -> {
        Relationships related = Relationships.builder()
                         .basketIds(IncentiveSearchBuilder.getCollectionFilterByPath(querySpec, "relatedBaskets.id"))
                .build();
        if (related.basketIds.size() == 1) {
            related.setBasketId(querySpec.findFilter(PathSpec.of("relatedBaskets.id"), FilterOperator.EQ).map(FilterSpec::getValue).map(UUID.class::cast).orElse(null));
        }

        final Collection<Predicate> predicates = new ArrayList<>();


        if (related.getBasketIds().size() > 0) {
            if (related.getBasketIds().size() > 1) {
                predicates.add(builder.and(root.get("baskets").get("basketId").in(related.getBasketIds())));
            } else {
                predicates.add(builder.equal(root.join("baskets").get("basketId"), related.getBasketId()));
            }
        }

        return builder.and(predicates.toArray(new Predicate[predicates.size()]));
    };
}

Note : relatedBaskets is the name present in IncentiveCouponCrnkModel and baskets is the column name present in the IncetiveCoupon table

When i call this

http://localhost:21120/api/marketing/incentive-coupons?filter=(EQ%20baskets.id%20%224b12f8a9-5b6a-47fa-a97c-2d4bc70e8da3%22)

The control first goes to IncentiveCouponCrnkRepository.findAll() method and then invokes the IncentiveSearchBuilder.findIncentiveCoupon() method which generates the query.

The query that is generated is as below

select incentivec0_.id as id1_4_, incentivec0_.brand as brand2_4_, incentivec0_.characteristics as characte3_4_, incentivec0_.code as code4_4_, incentivec0_.coupon_type as coupon_t5_4_, incentivec0_.created_at as created_6_4_, incentivec0_.distribution_target_id as distribu7_4_, incentivec0_.distribution_target_type as distribu8_4_, incentivec0_.external_id as external9_4_, incentivec0_.incentive_coupon_batch_id as incenti13_4_, incentivec0_.lifecycle_status as lifecyc10_4_, incentivec0_.name as name11_4_, incentivec0_.updated_at as updated12_4_ from incentive_coupons incentivec0_ inner join incentive_coupon_baskets baskets1_ on incentivec0_.id=baskets1_.incentive_coupon_id where baskets1_.basket_id=?

The problem with the query is it does not include a column from the IncentiveCouponBasket table, i expect basket_id column from IncentiveCouponBasket table in the above query. All the columns in the above select query are from IncentiveCoupon table

My Questions

• Why is the column of IncentiveCouponBasket(basket_id) not part of the query

• Should we write a named query to include that column

• Also the UUID in the table definition are defined as binary and in mariadb they are stored as blobs. I am unable to the convert the blob types to UUID while i query the mariadb

  select incentivec0_.id as id1_4_, incentivec0_.brand as brand2_4_, incentivec0_.characteristics as characte3_4_, incentivec0_.code as code4_4_, incentivec0_.coupon_type as coupon_t5_4_, incentivec0_.created_at as created_6_4_, incentivec0_.distribution_target_id as distribu7_4_, incentivec0_.distribution_target_type as distribu8_4_, incentivec0_.external_id as external9_4_, incentivec0_.incentive_coupon_batch_id as incenti13_4_, incentivec0_.lifecycle_status as lifecyc10_4_, incentivec0_.name as name11_4_, incentivec0_.updated_at as updated12_4_, incentive_coupon_baskets.basket_id as basket_id from incentive_coupons incentivec0_ inner join incentive_coupon_baskets on incentivec0_.id=incentive_coupon_baskets.incentive_coupon_id

  where incentive_coupon_baskets.basket_id='4b12f8a9-5b6a-47fa-a97c-2d4bc70e8da3'

The above query also does not return data event though the basket_id has the same value as in the database. Since it is stored as blob type while using the id it does not return data.