Additional conditions in JOIN

Question

I have tables with articles and users, both have many-to-many mapping to third table - reads.

What I am trying to do here is to get all unread articles for particular user ( user_id not present in table reads ). My query is getting all articles but those read are marked, which if fine as I can filter them out (user_id field contains id of user in question).

I have an SQL query like this:

SELECT articles.id, reads.user_id 
FROM articles 
LEFT JOIN 
reads 
ON articles.id = reads.article_id AND reads.user_id = 9 
ORDER BY articles.last_update DESC LIMIT 5;

Which yields following:

    articles.id    | reads.user_id
-------------------+-----------------
 57125839          |       9
 57065456          |
 56945065          |
 56945066          |
 56763090          |
(5 rows)

This is fine. This is what I want.

I'd like to get same result in Catalyst using my article model, but I cannot find any option to add conditions to a JOIN clause.

Do you know any way how to add AND X = Y to DBIx JOIN?

I know this can be done with custom resoult source and virtual view, but I have some other queries that could benefit from it and I'd like to avoid creating virtual view for each of them.

Thanks, Canto

Answer 1

I got an solution.

It's not straight forward, but it's better than virtual view.

http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Relationship/Base.pm#condition

Above describes how to use conditions in JOIN clause. However, my case needs an variable in those conditions, which is not available by default in model. So getting around a bit of model concept and introducing variable to it, we have the following.

In model file

our $USER_ID;
__PACKAGE__->has_many(
    pindols => "My::MyDB::Result::Read",
    sub {
        my $args = shift;

        die "no user_id specified!" unless $USER_ID;

        return ({
            "$args->{self_alias}.id" => { -ident => "$args->{foreign_alias}.article_id" },
            "$args->{foreign_alias}.user_id" => { -ident => $USER_ID },
        });

    }
);

in controller

$My::MyDB::Result::Article::USER_ID = $c->user->id;
$articles = $channel->search(
    { "pindols.user_id" => undef } , 
    { 
        page => int($page),
        rows => 20, 
        order_by => 'last_update DESC', 
        prefetch =>  "pindols" 
    }
);

Will fetch all unread articles and yield following SQL.

SELECT me.id, me.url, me.title, me.content, me.last_update, me.author, me.thumbnail, pindols.article_id, pindols.user_id FROM (SELECT me.id, me.url, me.title, me.content, me.last_update, me.author, me.thumbnail FROM articles me LEFT JOIN reads pindols ON ( me.id = pindols.article_id AND pindols.user_id = 9 ) WHERE ( pindols.user_id IS NULL ) GROUP BY me.id, me.url, me.title, me.content, me.last_update, me.author, me.thumbnail ORDER BY last_update DESC LIMIT ?) me LEFT JOIN reads pindols ON ( me.id = pindols.article_id AND pindols.user_id = 9 ) WHERE ( pindols.user_id IS NULL ) ORDER BY last_update DESC: '20'

Of course you can skip the paging but I had it in my code so I included it here.

Special thanks goes to deg from #dbix-class on irc.perl.org and https://blog.afoolishmanifesto.com/posts/dbix-class-parameterized-relationships/.

Thanks, Canto

Answer 2

I don't even know what Catalyst is but I can hack the SQL query:

select articles.id, reads.user_id 
from
    articles 
    left join 
    (
        select *
        from reads
        where user_id = 9
    ) reads on articles.id = reads.article_id
order by articles.last_update desc 
limit 5;