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!

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]
      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]
      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]
        You use SQL and carry on so your project remains relevant.
[reply]

Back to Seekers of Perl Wisdom