DBIx::Class query question

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

Hi,

I am having problem putting together a DBIx::Class query that uses a mysql-function in the where-clause.

Here is my resultset:

package MyDB::Schema::Result::Queue;
use strict;

use base qw/DBIx::Class::Core/;

__PACKAGE__->table('queue');
__PACKAGE__->add_columns( id => { is_auto_increment => 1},
                         qw/ msg box /);
__PACKAGE__->set_primary_key('id');

1;
[download]

The query I want to (re-)construct is select id, msg, box from queue where is_lock_free(box) order by id limit 1

The best I can get at the moment is

my $rs = $schema->resultset('Queue');

my $q = $rs->search ({ -bool => "is_free_lock(me.box)" }, { order_by =
+> 'id', rows => 1 })->single;
[download]

Which produces SELECT me.id, me.msg, me.box FROM queue me WHERE ( is_free_lock(me.box) ) ORDER BY id LIMIT 1 which actually works but of course is obtained by cheating because I supplied the me.box argument to is_lock_free by hand...

How can I do this without cheating?

Many thanks!

Comment on DBIx::Class query question Select or Download Code

Replies are listed 'Best First'.
Re: DBIx::Class query question by Your Mother (Archbishop) on Dec 31, 2010 at 05:02 UTC
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.	[reply] [d/l]
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]