This kind of leaves devs in a bind then because one of the great things about active_record was you could do GROUP CONCAT, DISTINCT, REPLACE etc on fields in the selection area or on WHERE clauses.
This really busts apart the usefulness of Models. You would have to just do a ton of post processing and increase memory and query size.
Thanks for looking into it.
Hopefully I understand correctly, but what you’re asking for is basically just a camelCased query builder, correct? Possibly with model name > table name translation.
Greg, would that summarize what you need as well? Doing these kinds of queries on the model directly unfortunately doesn’t make sense, since a DISTINCT query doesn’t return a specific row / entity, so opening up save() or delete() on that result is a little more tricky.
I reckon just a few are aggregates. There’s plenty of other, more custom stuff that should be possible: using IFs in SELECT, GROUP BY with HAVING, UNIONs, MATCH AGAINST, or even Haversine methods. The list goes on. There should be a way, like with Active Record, to continue using queries like that.
I reckon just a few are aggregates. There’s plenty of other, more custom stuff that should be possible: using IFs in SELECT, GROUP BY with HAVING, UNIONs, MATCH AGAINST, or even Haversine methods. The list goes on. There should be a way, like with Active Record, to continue using queries like that.
This echoes my needs as well. I am trying to get comfortable with “please use our models as much as possible so we can change schema whenever” but not having these abilities from SQL completely breaks my ability to use the models as much as possible. Right now we are continuing to use Active Record much of the time in hopes that there is at least a replacement for that if models won’t get the functionality we need.
I am not sure i understand how distinct wouldn’t work your current model structure. DISTINCT is used when you are getting multiple results, thus it would be a collection like an ->all() call is right now. What am I missing?
I am not sure i understand how distinct wouldn’t work your current model structure. DISTINCT is used when you are getting multiple results, thus it would be a collection like an ->all() call is right now. What am I missing?
Interestingly enough, it’s the same as this thread. Generating the actual query would be simple, but it can’t be turned into a proper model result. Part of the architectural backbone for the models is that everything has a primary key. That key is used to support calls to save()
and delete()
. The fix for that thread was that the key is always added to the selected fields, so for distinct you would now end up with SELECT DISTINCT primary_key, other_fields ...
. That means everything is unique, and the distinct doesn’t do anything. So the only time it would work as expected is for SELECT DISTINCT *
, but then I would question why there are identical rows in that table to begin with.
We could approach this by skipping the primary_key addition for distinct queries, or by constructing an equivalent query using a GROUP BY
on the distinct columns instead. For both of those you either end up with no primary key, or an arbitrary primary key. That means the save/delete lifecycle methods don’t really behave as expected.
You guys have definitely made some good points though, and AR will inevitably require a replacement, so we won’t leave you high and dry on this.
That means everything is unique, and the distinct doesn’t do anything
I am asking for ->fields('DISTINCT(columnName)')
for items that are not the primary key. You could still return the primary key and have a distinct set of results based on distinct (non-primary key) column. I am not using ->distinct()
with active record, more i am doing ->select('column1, DISTINCT(column2)')
.
The result would be the collection, unique on column2 and the assumption would be if i did ->delete()
it would only delete the resulting items, no?
Hopefully I understand correctly, but what you’re asking for is basically just a camelCased query builder, correct?
I wouldn’t necessarily be dissatisfied with this, however you might consider:
Model extends the query builder
In Laravel, Eloquent extends Fluent so Fluent methods are available in Eloquent models, which can be very handy if you need specific queries (usually the R in CRUD) but 95%+ of everything else is handled by the model. Could EE’s Model extend the new query builder?
PDO bindings
Instead of error-prone regex for escaping db parameters.
Syntactic sugar
For convenience when working with resultsets and table joins. Back in the day, when building CI apps I always ended up extending AR to do stuff like this. Maybe the table relationship definitions could be part of the query builder that Model then extends, so both can use it.
I will echo what Mark said. I kind of came in expecting that an AR style query builder would be how the new Model structure would go down because thats how so many people used CI at the end and how you can do it with Eloquent. It would let me use the new models 99% of the time instead of 50%.
i am doing ->select(‘column1, DISTINCT(column2)’)
That would only be implementable as a group by! 😉
The result would be the collection, unique on column2 and the assumption would be if i did ->delete() it would only delete the resulting items, no?
I guess what I don’t see is when that is of use with a model based result? You either want a list of the unique values in that column, with little care for the other items in those rows, or you want another column’s aggregate with respect to your distinct column. That second case would be:
SELECT field1, COUNT(field2) as arbitrary_name FROM exp_table GROUP BY field1
That leaves me with an immutable arbitrary_name field on the model and lots of null fields. A model just doesn’t seem like the right object to return. Something a little more reduced in scope, like AR? Sure. See below.
I wouldn’t necessarily be dissatisfied with this, however you might consider: …
I should’ve clarified that I was definitely thinking new code, not some aliasing scheme 😊 . The model select queries have a bunch of workarounds for AR’s quirks that I would love to get rid of. And the switch to PDO was with prepared statements in mind.
The one thing I would push against, as you might guess from the rest of the thread is Model extends QueryBuilder
, which I’m interpreting as ModelBuilder extends QueryBuilder
. I like the idea of automatically matching model names and fields to the correct tables and extracting certain relationship information (both of which we already have to do to query for models). But if a result can’t reasonably be hydrated as a model I would really prefer not to force it. That means the model builder would have to disable a bunch of potential query methods which would be unfortunate. So there would definitely be some sharing, if not least of all for consistencies sake, but the model api should remain one that’s hard to screw up.
Packet Tide owns and develops ExpressionEngine. © Packet Tide, All Rights Reserved.