The last post covered some of the basics of aggregate commands, but left out a section explaining the more perilous aggregates of associations and more advanced querying against them.
Here are a few questions to get you thinking before we start. Given a model Foo which
(key, value, foo_id):
- How do we find the count of tags for every Foo?
- How do we find a Foo with multiple matching tags? (name: ‘David Tennant’ AND color: ‘Blue’)
Suddenly ActiveRecord becomes very annoyingly complicated to use, but not to fear! We can still use SQL for all of this.
So now our application looks something like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
A Path Lesser Traveled
Normally when you start to look into ActiveRecord Queries, you’re going to see some code that looks something like this:
or perhaps you’ll see the normal escaped queries:
…but lurking in the documentation you’ll find another way entirely that’s not so often advertised by guides, allowing us the same power as the string based conditionals with what I would argue as a lot more clear way.
So why not just use the first variant like any sane developer, you might wonder. Put simply, because this allows us the full leverage of string conditionals with a lot more clarity. Try and do this with the hash syntax:
1 2 3 4
Say you want the count of tags on a foo, how would you go about it?
Now the conundrum here is why do we need to use group? Let’s take a look at the generated SQL:
In order to run a count on an association, we need to aggregate the records into groups that we’ll run the count against. Handy thing is, this allows us to do a few more… interesting things:
1 2 3
You can run aggregates for different groups, not just the supposedly common case, hence why AR wants you to specify it. In the first case, we’re simply telling it to aggregate the tags based on the id of their parent.
Finding multiple matching tags
I’m going to put a disclaimer here and say that trying to play for Single Table Inheritance hacks like this will cause you a lot more harm than good. Now if your data model is not so friendly and forces you into this, it’s something worth remembering.
Aliased Inner Joins
SQL has a concept for this, but AR currently does not give us this power. Thankfully we have access to
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
Now note that this is most certainly not the best way to go about this, suggestions are quite welcome as to better ways to deal with this one.
What we’re doing here is in essence creating an on-the-fly single table inheritance to query against.
Admittedly a better way to do this currently eludes me, and I would recommend against using this on your own solutions.
One can use subqueries to circumvent this type of issue, but the solution will be similar if not slower.
As you can see, this design of ours quickly devolves into madness when querying against at the end of the article. In the next sections, I’ll be covering methods of database design to avoid these issues as much as possible.