Additional conditions in JOIN

1
votes

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

2
perlpostgresqlcatalystdbix-class
Have you tried adding it as a "standard" where clause? - Len Jaffe
Yes. "Standard" where clause will filter only those articles that are read by the user (exists in reads table). It will not show articles where corresponding reads table entry is NULL. Using AND with LEFT JOIN will give me ALL articles. User_id field will be set if article is read, if not it will be NULL. - canto
You can't use AND reads.user_id = 9 condition in the query if you want to get articles with reads.user_id = NULL. - krokodilko
@kordirko sure I can. As long as it is in JOIN clause. SELECT articles.id, reads.user_id FROM articles LEFT JOIN reads ON reads.article_id = articles.id AND reads.user_id = 9 ORDER BY articles.last_update DESC LIMIT 5; id | user_id ----------+--------- 57125839 | 9 57065456 | 56945065 | 56945066 | 56763090 | Sorry I have troubles formatting this comment... - canto
@kordirko unfortunately this will not work as it will filter out articles read by other users (other than id 9). The query in my original question is doing what I want. I just want to do the same using DBIx/Catalyst Model. - canto

2 Answers

1
votes

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;
1
votes

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