Re: [ebean] How to enforce LEFT JOIN

[Rob Bygrave]

This looks like a known bug that Daryl hit a while back. 

That is, due to the 'tags.text' being an OR condition ... that join really should be a LEFT join and it is not.

I suspect Daryl used raw sql to get around this issue in the meantime.


Cheers, Rob.

On 9 January 2013 22:57, P <pman...@googlemail.com> wrote:

Dear group,

iam trying to get some objects of the type "Card" from my database.

A Card has a @OneToMany Annotation to Tags.

Now iam trying to fetch some cards from the database, which have a specific search term in their keyword OR description OR (if they have any) the search term in their associated tags:

List<Card> cardList = Ebean.find(Card.class).fetch("tags")
.fetch("categories").where().eq("categories.id", id)
.disjunction().add(Expr.like("keyword", "%" + text + "%"))
.add(Expr.like("description", "%" + text + "%"))
.add(Expr.like("tags.text", "%" + text + "%"))
.orderBy("keyword").findList();

The resulting SQL query is:

select distinct t0.id c0, t0.keyword c1, t0.description c2, t0.revision c3, t0.created_date c4, t0.modified_date c5
        , t1.id c6, t1.title c7, t1.revision c8, t1.created_date c9, t1.modified_date c10 
from card t0
left outer join card_category t1z_ on t1z_.card_id = t0.id 
left outer join category t1 on t1.id = t1z_.category_id 
join card_category u1z_ on u1z_.card_id = t0.id 
join category u1 on u1.id = u1z_.category_id 
join tag u2 on u2.card_id = t0.id  
where u1.id = 2  and (t0.keyword like '%FU%'  or t0.description like '%FU%'  or u2.text like '%FU%' )  
order by t0.keyword

The problem with this is, that i dont get the cards in the results which dont have any associated tags.
If execute this query manually in my DBMS and alter "join tag u2" to "left join tag u2", i also get cards which haven't any tags.
Is there any way to enforce a LEFT JOIN in ebean or can i enforce this somehow else?

Thanks

[Daryl Stultz]

On Wed, Jan 9, 2013 at 5:29 AM, Rob Bygrave <robin....@gmail.com> wrote:

This looks like a known bug that Daryl hit a while back. 

Yup, looks like bug 402 to me.

I suspect Daryl used raw sql to get around this issue in the meantime.

Correct.

/Daryl

[Daryl Stultz]

On Sat, Jan 12, 2013 at 2:44 PM, Bathily Didier <bat...@gmail.com> wrote:

Hello Daryl,

could you please share how you use raw sql to get around this issue?

Hi, sorry for the delay. I can't seem to find the specific solution. It was probably something like this:

Ebean.find(MyEntity.class)

.where().raw("id in (select MyEntities.myEntityId" +

" from MyEntities" +

" left join OtherAs on OtherAs.aId = MyEntities.aId" +

" left join OtherBs on OtherBs.bId = MyEntities.bId" +

" where" +

(OtherAs.prop = 1 or OtherBs.prop = 1))")

.findList()

HTH

/Daryl

[Didier Bathily]

Thank you Daryl

I actually used something like that as a solution, but did not work here. In fact, it was another bug, paging list do not work with rawsql http://www.avaje.org/bugdetail-415.html.

Didier

[Bathily Didier]

I didn't find a good solution. I manually wrote the 2 query using raw sql to make my paging functionality in this case

Le samedi 11 mai 2013 16:19:03 UTC+2, Martin Malfertheiner a écrit :

Hey Didier,

I'm stuck at the same problem. I need a disjunction and the paging function.

Could you please tell me how you solved the problem?

Thank  you

[Brannon Smith]

Couldn't decide whether to reply to this thread or What's the deal with disjunction?

I also saw that you are pretty swamped right now, Rob, but... any idea when we can have conditions under disjunction generating LEFT OUTER JOINs rather than INNER JOINs? I was recently building tests for an elaborate search query builder and this bug really kicked my ass.

I take it the fix for this in ebean is non-trivial?

-GBS

[SamC]

Hi Brannon,

I started a similar thread here not long ago about a related issue, and Rob did respond on that one.  I'm not sure what a time frame from resolution would be.  Unfortunately, in our case, we couldn't afford to wait for a solution.  We just finished a migration to JPA/Hibernate in order to make this type of thing work properly.

Good luck,

Sam

[se...@sekora.io]

Is there a fix for this yet? I just ran into the same issue.

[Daryl Stultz]

On Thu, Feb 11, 2016 at 11:56 AM, <se...@sekora.io> wrote:

Is there a fix for this yet? I just ran into the same issue.

Does this look like the issue?

https://github.com/ebean-orm/avaje-ebeanorm/issues/37

 

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.