4

Forgive me if this is a stupid or naive question...

I generally think of an ElementCriteriaModel as being a "search object". Specifically, it's a search object which revolves around a specific type of element. The model has attributes like order, offset and limit, which are obviously common terms when conducting an SQL query.

On the other hand, a DbCommand is an object which directly translates to an SQL query. This is far more flexible, allowing you to do things like perform complex joins.

So while I can certainly see some differences, it's hard for me to grasp the finer points of one vs. the other. (I'll admit that typing this has helped me to see it a little more clearly.)

What am I missing/misunderstanding?

Lindsey D
  • 23,974
  • 5
  • 53
  • 110

2 Answers2

3

DbCommand represents a SQL statement to execute against a database.

ElementCriteriaModel is a model just like any other model in craft/app/models, but it also happens to make it easy to search for Elements in the database.

By setting all of the criteria for the elements you're searching for and calling the find() method, that will invoke craft()->elements->findElements($this);, which in turn will create a DbCommand object custom built for the ElementCriterialModel parameters that you set.

So think of ElementCriteriaModel as an abstraction on top of DbCommand that makes it easy to search for Elements.

Brad Bell
  • 67,440
  • 6
  • 73
  • 143
  • Thanks Brad, great explanation! Is there any way via ElementCriteriaModel to perform a join with another table? The abstraction is great, but it seems to come with a few limitations. – Lindsey D Sep 26 '14 at 18:04
  • Not explicitly, no. Joins may or may not be created as a by-product of the parameters set on the model, though. – Brad Bell Sep 26 '14 at 18:06
  • You're saying that the ElementCriteriaModel could very likely create some inherent joins in its standard operations. Is there a reasonable way to tack on an additional join after it's been parsed into a DbCommand? Or would it be better to directly run a DbCommand if I want to do something crazy like that? – Lindsey D Sep 26 '14 at 18:11
  • On a similar note, is it possible to "intercept" the DbCommand that gets generated, between the time that it's generated and the time that it's executed? – Lindsey D Sep 26 '14 at 18:13
  • Yeah, I think you're better off using DbCommand to craft (get it?) the exact query you need. Re: intercepting, possible, depending on if you want it before it's been parsed down to SQL. But you'd be extending DbCommand with your own logic to override. – Brad Bell Sep 26 '14 at 18:21
  • @BradBell On a related note, would it be a good idea in the future to have some official example of how to do what Lindsey mentions, if at all possible? For example, have Craft do most of it's magic, but then join in some custom data from a join or something. I already have several contexts where I simply just wanted to tack on some attributes on an existing model. – Fred Carlsen Sep 26 '14 at 18:43
  • 1
    @LindseyD Worth mentioning that if you're in the context of a custom element type, then you've got the modifyElementsQuery() method that you can override to do your own query modifications. – Brad Bell Sep 26 '14 at 18:46
  • @FredCarlsen Yeah, would probably fall in with the custom element type documentation that needs to be written. – Brad Bell Sep 26 '14 at 18:46
  • @FredCarlsen Just discovered buildElementsQuery, which works perfectly in this case! I posted a separate SE question/answer to show how it works... – Lindsey D Sep 27 '14 at 20:59
1

I assume that one advantage would be that as Craft gets updated, the ElementCriteriaModel is updated whereas you may need to change any DBCommand queries you have created.

David A McInnis
  • 1,201
  • 12
  • 26
  • Thanks David, that's certainly one aspect of it. I feel like there are probably some other nuances to be aware of as well. – Lindsey D Sep 26 '14 at 16:41
  • You do touch on a great point... P&T reserves the right to change their database schema at any time, in any way. If you're conducting your queries entirely via ElementCriteriaModel, then you wouldn't have to worry about any schema changes. On the other hand, any DBCommands may have to be completely rewritten. Thanks! – Lindsey D Sep 26 '14 at 16:44