CODEWITHSUNDEEP
postgresqldbix-class
FoohonPie
FoohonPie

Reputation: 11

group_by or distinct with postgres/dbix-class

I have a posts table like so:

+-----+----------+------------+------------+
|  id | topic_id |    text    |  timestamp |
+-----+----------+------------+------------+
| 789 |     2    |     foobar | 1396026357 |
| 790 |     2    |     foobar | 1396026358 |
| 791 |     2    |     foobar | 1396026359 |
| 792 |     3    |     foobar | 1396026360 |
| 793 |     3    |     foobar | 1396026361 |
+-----+----------+------------+------------+

How would I could about "grouping" the results by topic id, while pulling the most recent record (sorting by timestamp desc)?

I've come to the understanding that I might not want "group_by" but rather "distinct on". My postgres query looks like this:

select distinct on (topic_id) topic_id, id, text, timestamp 
from posts 
order by topic_id desc, timestamp desc;

This works great. However, I can't figure out if this is something I can do in DBIx::Class without having to write a custom ResultSource::View. I've tried various arrangements of group_by with selects and columns, and have tried distinct => 1. If/when a result is returned, it doesn't actually preserve the uniqueness.

Is there a way to write the query I am trying through a resultset search, or is there perhaps a better way to achieve the same result through a different type of query?

Upvotes: 1

Views: 562

Answers (1)

kbenson
kbenson

Reputation: 1464

Check out the section in the DBIC Cookbook on grouping results.

I believe what you want is something along the lines of this though:

my $rs = $base_posts_rs->search(undef, {
    columns  => [ {topic_id=>"topic_id"}, {text=>"text"}, {timestamp=>"timestamp"} ],
    group_by => ["topic_id"],
    order_by => [ {-desc=>"topic_id"}, {-desc=>"timestamp"} ],
})

Edit: A quick and dirty way to get around strict SQL grouping would be something like this:

my $rs = $base_posts_rs->search(undef, {
    columns  => [
        { topic_id  => \"MAX(topic_id)" },
        { text      => \"MAX(text)" },
        { timestamp => \"MAX(timestamp)" },
    ],
    group_by => ["topic_id"],
    order_by => [ {-desc=>"topic_id"}, {-desc=>"timestamp"} ],
})

Of course, use the appropriate aggregate function for your need.

Upvotes: 1

Related Questions