in reply to DBIx::Class query question

Completely untested and I've never tried this before so it's pure guesswork- 

my $rs = $schema->resultset("Queue")
    ->search({ -bool => "free_lock" },
             { "+select" => [{ is_free_lock => "box", -as => "free_loc
+k" }],
               "+as" => [qw/ is_free_lock /],
               order_by => "id",
             });

my $item = $rs->first;

[download]

Please let us know if it does work. If it doesn't you might look in the database function section of DBIx::Class::Manual::Cookbook.

Replies are listed 'Best First'.
Re^2: DBIx::Class query question
by morgon (Priest) on Dec 31, 2010 at 11:06 UTC
    Many thanks for your reply, it is definitely an improvement but unfortunately is does not (yet) work.

    Here the sql that is generated and the exception that is thrown:

    SELECT me.id, me.msg, me.box, me.free_lock, IS_FREE_LOCK( box ) AS fre
+e_lock FROM queue me WHERE ( free_lock ) ORDER BY id: 
DBIx::Class::ResultSet::first(): DBI Exception: DBD::mysql::st execute
+ failed: Unknown column 'me.free_lock' in 'field list' [for Statement
+ "SELECT me.id, me.msg, me.box, me.free_lock, IS_FREE_LOCK( box ) AS 
+free_lock FROM queue me WHERE ( free_lock ) ORDER BY id"]

    [download]
    As a band-aid I have tried to add a column free_lock to the resultset (which of course would be cheating again) but no no avail.

    Unfortunately the Cookbook only has examples for using database functions in the select-clause of the sql, but I need it in the where-clause...

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom