SQL::Abstract-ing subqueries

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

Hello Monks,

I'm trying to find the best way to represent an SQL query using SQL::Abstract (actually SQL::Abstract::More via DBIx::DataModel; SQL::Abstract is also used by DBIx::Class). Unfortunately there is not a great deal of information on constructing SQL queries that fall outside the bounds of the usual 'SELECT * FROM x WHERE ...' using SQL::Abstract.

My ideal query:

SELECT 
  CASE
    WHEN record.is_public = 'Yes' THEN 'public'
    WHEN EXISTS ( SELECT 1
      FROM permissions
      WHERE permissions.rec_id = record.id AND user_id = $user_id )
      THEN 'accessible'
  ELSE
    'private'
  END
  AS viewable,
 record.id,
 record.display_name
FROM
record
WHERE 
record.id = $rec_id; # or other criteria
[download]

...where $user_id and $rec_id (or other WHERE criteria) are supplied by the Perl code.

Has anyone used SQL::Abstract for this kind of query?

Thanks!

Comment on SQL::Abstract-ing subqueries Select or Download Code

Replies are listed 'Best First'.
Re: SQL::Abstract-ing subqueries by poj (Abbot) on Feb 03, 2017 at 22:08 UTC
I guess you are looking for something smarter than this :) `#!perl use strict; use DBI; use SQL::Abstract::More; my $sqla = SQL::Abstract::More->new(); my $case = q! CASE WHEN record.is_public = 'Yes' THEN 'public' WHEN EXISTS ( SELECT 1 FROM permissions WHERE permissions.rec_id = record.id AND user_id = ? ) THEN 'accessible' ELSE 'private' END as viewable!; my ($sql,undef) = $sqla->select( -columns => [$case,'record.id','record.display_name'], -from => 'record', -where => { 'record.id' => 1 } ); print $sql;` [download] poj	[reply] [d/l]
Re^2: SQL::Abstract-ing subqueries by Anonymous Monk on Feb 04, 2017 at 01:18 UTC
I am using something pretty similar at present and was hoping there was a more SQL::Abstract-y way of doing things! Thank you, though. :)	[reply]
Re: SQL::Abstract-ing subqueries by flowdy (Scribe) on Feb 07, 2017 at 09:15 UTC
Hi, You might consider using a view stored inside the database in favour of application-side querying. Then just realize that view as a normal table relation in the ORM model, but observe not to accidentally touch the data, as view data is immutable i.e. dynamic. In DBIx::Class for instance there is also a DBIx::Class::ResultSource::View class, but I cannot see any use in it beyond deployment from ORM. I recently made a ORM model for a SQLite3 database with a considerable number of views. Works like a charm and, thanks to database triggers as well, the application code is quite free of data-related logic and querying stuff.	[reply]
Re: SQL::Abstract-ing subqueries by Anonymous Monk on Feb 03, 2017 at 19:29 UTC
"Unfortunately there is not a great deal of information on constructing SQL queries that fall outside the bounds of the usual" Perhaps they all finally figured out what a bad idea trying to abstract SQL is. There is nothing wrong with SQL.	[reply]
Re^2: SQL::Abstract-ing subqueries by Anonymous Monk on Feb 03, 2017 at 20:48 UTC
Agreed, but given that I am working with a database ORM that uses SQL::Abstract to represent SQL queries and asked a specific question about it, your points are not relevant.	[reply]
Re^3: SQL::Abstract-ing subqueries by Anonymous Monk on Feb 03, 2017 at 21:01 UTC
You use SQL and carry on so your project remains relevant.	[reply]


Syntactic Confectionery Delight
	PerlMonks