CODEWITHSUNDEEP
javasap-commerce-cloudcommercehybris-data-hubflexible-search
siddarth
siddarth

Reputation: 1

join in flexi search is not working with is null keyword

I have a table named as aviationDispute which has 2 columns deliveryId type=aviationdelivery and invoiceId type=AviationB2BDocuments , now when we raise a dispute from post man it will be eith against a delivery or either against an invoice both the columns can never be filled in one dispute .

The main problem is : my flexi query is only working when both the columns are filled , if one is filled and one remains then it does not gives result .

My flexi query is below

select {p:pk} from {AviationDispute as p join AviationB2BDocuments as a on {p:invoiceId}={a:pk} join AviationDelivery as d on {p:deliveryId} = {d:pk}} where ({d:deliveryId} LIKE '%searchTerm%' or {a:invoiceNumber} LIKE '%searchTerm%')

I have tried various combos of is null and is not null with brackets but nothing is working

Upvotes: 0

Views: 735

Answers (2)

Adiputera
Adiputera

Reputation: 196

What you need is left join

select {p:pk} from {
   AviationDispute as p 
   left join AviationB2BDocuments as a on {p:invoiceId}={a:pk} 
   left join AviationDelivery as d on {p:deliveryId} = {d:pk}
} 
where 
   {d:deliveryId} LIKE '%searchTerm%' or {a:invoiceNumber} LIKE '%searchTerm%'

Upvotes: 1

Devendra
Devendra

Reputation: 1884

hope following query would help:

 select {p:pk} from {AviationDispute as p join AviationB2BDocuments as a on {p:invoiceId}={a:pk} join AviationDelivery as d on {p:deliveryId} = {d:pk}} where {d:deliveryId} LIKE '%searchTerm%' and {a:invoiceNumber} is null or {a:invoiceNumber} LIKE '%searchTerm%'

deliveryId will never be null in the result, because of natural join

Upvotes: 0

Related Questions