[Dbix-class] Re: how to use a complex query in DBIx::Class

[A. Pagaltzis]

* Matt LeBlanc <mleblanc at cpan.org> [2006-12-30 08:10]:
> Sorry, I just realized I used the wrong field. Of course, this
> doesn't matter as if the join condition fails, anything from
> hiddenboards is null. Here is a full example of how this would
> work:
> 
> CREATE TABLE boards (
> 	id int,
> 	active int,
> 	pos int
> );
> 
> INSERT INTO boards VALUES(1,1,1);
> INSERT INTO boards VALUES(2,1,2);
> INSERT INTO boards VALUES(3,1,3);
> 
> CREATE TABLE hiddenboards (
> 	userid int,
> 	boardId int	
> );
> 
> INSERT INTO hiddenboards VALUES(1,1); -- not userid 3, so no join
> INSERT INTO hiddenboards VALUES(3,2); -- userid 3, so valid join
> -- no entry for id 3 so no join
> 
> SELECT boards.id,CASE WHEN hiddenboards.userid is not null THEN 1 ELSE 0 END AS hidden
> FROM boards LEFT JOIN hiddenboards
> ON hiddenboards.userid = 3
> AND hiddenboards.boardid = boards.id
> WHERE boards.active = 1
> ORDER BY pos;
> 
> In this particular case, the rows returned are as follows:
> 
> id,hidden
> 1,0
> 2,1
> 3,0

You’re abusing the ON clause. You’re not joining on the userid
condition; you’re constraing the result set. You should put it in
the WHERE clause:

    SELECT
        b.id,
        CASE WHEN h.userid IS NOT NULL THEN 1 ELSE 0 END AS hidden
    FROM boards b
    LEFT JOIN hiddenboards h ON h.boardid = b.id
    WHERE
        b.active = 1
        AND h.userid = 3
    ORDER BY b.pos

But this is really an abuse of joining, because you’re not
actually interested in joining data, only in checking, which
should be written as a correlated subquery:

    SELECT
        b.id,
        EXISTS (
            SELECT NULL
            FROM hiddenboards h
            WHERE
                h.userid = 3
                AND h.boardid = b.id
        ) AS hidden
    FROM boards b
    WHERE b.active = 1
    ORDER BY b.pos

Note that I use EXISTS and select nothing but NULL to make it
clear that the only fact of interest is whether a matching row
exists in `hiddenboards`.

In that way it is self-documenting what you’re trying to ask for.

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>