Using DBIx::Class For Slightly Complex Queries

phildeman has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I am struggling to use DBIx::Class for somewhat of a complex query

This is the SQL statement I want to use:

SELECT distinct d.deptid, d.dept_name
FROM rooms r
     LEFT JOIN departments d ON r.dept=d.deptid
WHERE d.deptid != 1 OR d.dept != 3 OR d.dept !=42
[download]

If the WHERE clause is too much of an issue, I can always use Perl to remove the specific records I don't need.

Can someone help me convert this into Perl code to use with DBIx::Class.

Thanks for your help.

-Phil-

Comment on Using DBIx::Class For Slightly Complex Queries Download Code

Replies are listed 'Best First'.
Re: Using DBIx::Class For Slightly Complex Queries by poj (Abbot) on Aug 13, 2015 at 19:03 UTC
I'm not sure what you are trying to filter but `(d.dept != 3 OR d.dept != 42)` will always be true which makes the WHERE statement redundant. I'm guessing you want `WHERE d.deptid != 1 AND d.dept != 3 AND d.dept != 42` poj	[reply] [d/l] [select]
Re^2: Using DBIx::Class For Slightly Complex Queries by jeffa (Bishop) on Aug 13, 2015 at 19:16 UTC
Or perhaps even `WHERE d.deptid != 1 AND d.dept NOT IN (3, 42)` [download] jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l]
Re^2: Using DBIx::Class For Slightly Complex Queries by phildeman (Scribe) on Aug 13, 2015 at 20:50 UTC
Thanks. The WHERE clause should have AND. Can you help or guide me with the Perl code for DBIx::Class? Thanks. -Phil-	[reply]
Re: Using DBIx::Class For Slightly Complex Queries by rnewsham (Curate) on Aug 14, 2015 at 11:06 UTC
Here is an example of what I think you are trying to achieve. This is rough untested code so may not work but should give you some idea of where to go next. If you post your attempt at the query, with details of the errors you are getting, someone may be able to provide more help. `Schema::Room->search( { departments.deptid => { '!=', 1 }, -or => [ departments.dept => { '!=', 3 }, departments.dept => { '!=', 42 }, ], }, { join => ['departments'] group_by => 'departments.deptid', } );` [download]	[reply] [d/l]