filtering entityload on a related entity

[salvatore fusto]

Hi,
suppose i have an author entity with a one-to-many relation with an article entity: surely i can use entityload("author") filtering on one of its property value, but is it possible to use entityLoad("author") filtering on a property of the related articles?
example: entityLoad("author",{name="Bob"}) filters authors on name, but how ca i load authors with articles where ie dataPublished property (of articles) has a certain value?
tnx and regards
salvatore
ps
time ago i've bought coldfussion ORM book: does the book contains these filtering criteria?

[Cameron Childress]

If you really want to stick with ORM, you can use ormExecuteQuery() for this. This is covered in the John Whish book if that's the book you are talking about.

But honestly, for this sort of situation I tend to just use CFQUERY and SQL. I will generally only use ORM for more basic "one entity" things and SQL for "lists of things". There is nothing wrong with mixing them up when it makes sense.

-Cameron

--
You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cf-orm-dev+unsubscribe@googlegroups.com.
To post to this group, send email to cf-or...@googlegroups.com.
Visit this group at https://groups.google.com/group/cf-orm-dev.
For more options, visit https://groups.google.com/d/optout.

--

Cameron Childress
p:   678.637.5072

[salvatore fusto]

hi, thanks for this replay.
i already mix orm and pure sql query, expecially in complex queries, just as you wrote, but in some not too complex cases i tend to use orm: as example, consider 3 entities:
A)author
P)Post
C) post Category
A in one-to-many with P, and C in one-to-many with P: i'd know if it is possible to use entityLoad("author), with only posts of one and only one category: this should be surely much simpler than q sql join-ed query
thanks and regards
salvatore

[Christian Kostenzer]

did you have a look to HQL ? This works similar to „plain“ ORM and gives you the same flexibility than plain SQL.

regards

__________________________________________________________

Mag.(FH) Christian Kostenzer
Gärberbach 1
6020 Innsbruck Land I

fon: +43 676 3327320
fax: +43 810 9554 249195
Email: chri...@kostenzer.net
Web: www.kostenzer.net
UID: ATU56508014

--
You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.

To unsubscribe from this group and stop receiving emails from it, send an email to cf-orm-dev+...@googlegroups.com.

[Markus Schneebeli]

Hi Salvatore

Look into Coldbox/Criteria Builder: It's exactly what makes your life easier:

https://github.com/coldbox-modules/cbox-cborm/wiki/ColdBox-Criteria-Builder

Mabe it takes a bit until you're into it, but afterwards.....

Best 

Markus

--
You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.

To unsubscribe from this group and stop receiving emails from it, send an email to cf-orm-dev+unsubscribe@googlegroups.com.

[Cameron Childress]

On Wed, Nov 29, 2017 at 2:32 AM, salvatore fusto wrote:

this should be surely much simpler than q sql join-ed query

Yet - if it were "much simpler", you would be done with this code instead of asking for help with it. How many times could you have written this in SQL since you started this email thread? ORM boils down to SQL anyway. So the only question is who's writing that SQL. Hibernate or you?

In my personal experience, as soon as you start joining up entities in ORM, things get exponentially more complex and harder to debug. The payoff just isn't there (for me). Once I start sliding down the rabbit hole of complex HQL I just switch back to SQL. You get the same result and it takes less code and debugging time.

There are lots of tools and helpers to get the HQL going and debug it, but at a certain point it's just easier to use SQL. 

That's my opinion anyway.

-Cameron

[Jon Clausen]

I’m going to second Criteria builder, it makes queries like this fairly straightforward, and allows for dynamic assembly much easier than HQL

 

.  It can be used outside of Coldbox, by just adding a mapping and then you can use the Virtual Entity Service to access all of the available Base ORM Service methods.   Using your original example, the criteria with the join would look something like this:

var authorService = new cborm.models.VirtualEntityService( "author" );

var c = authorService.newCriteria();

var r = c.restrictions;

 

c.resultTransformer( C.DISTINCT_ROOT_ENTITY );

 

c.add( r.eq( "name", "Bob" ) );

c.createAlias( "articles", "articles" );

c.add( r.eq( "articles.datePublished", arguments.publishDate ) );

 

return c.list( asQuery=false );

To unsubscribe from this group and stop receiving emails from it, send an email to cf-orm-dev+...@googlegroups.com.

To post to this group, send email to cf-or...@googlegroups.com.
Visit this group at https://groups.google.com/group/cf-orm-dev.
For more options, visit https://groups.google.com/d/optout.

 

--

You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.

To unsubscribe from this group and stop receiving emails from it, send an email to cf-orm-dev+...@googlegroups.com.

[salvatore fusto]

First of all, thank you for you kind replay and time.
surely cameron is right, i just do so now, but my approach is to have all things encapsulated so managing api's for all aspects of an app.
cb-cborm seems to be a good approach: i had a fast look on docs.
thanks and regards to all.
salvatore

[Phill Nacelli]

You can do this with straight out HQL query, make sure to use the "select distinct [authorAlias]" part to tell Hibernate to get an array of Author entities back, otherwise you'll get the full object graph for each entity including the Articles:

hql = "
select distinct
au
from
Author au
join auth.articles ar
where
au.name = :authorName and
ar.datePublished = :datePublished
";

authors = ormExecuteQuery(hql, {
authorName:"Bob",
datePublished:"12/01/2017"
});