[Dbix-class] Re: help needed in forming a 'and' and 'or' select query using DBIx::Class
Michele Beltrame m...@cattlegrid.info writes:
Hello!
columnA = x AND ( columnB = zz OR columnB LIKE '%mm%' )
It should be like this:
$val = 'mm';
$my_rs = $schema-resultset('Mytable')-search({
columnA = 'x',
-or = [
columnB = 'zz',
columnB = { like = '%'.$val.'%' }
],
});
Or a slightly more concise version:
my $rs = $schema-resultset('MyTable')-search({
columnA = 'x',
columnB = [ 'zz', { like = '%'.$val'%' } ],
});
--
The surreality of the universe tends towards a maximum -- Skud's Law
Never formulate a law or axiom that you're not prepared to live with
the consequences of. -- Skud's Meta-Law
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] help needed in forming a 'and' and 'or' select query using DBIx::Class
On Fri, Jan 18, 2013 at 10:19:25AM -0800, Rajeev Prasad wrote:
Hello,
I am trying to build a query based on two column values as folows:
columnA = x (one char, any char - not null)
columnB = zz _or_ x,xx,yyy,mm (comma seperated values or just one value)
I am looking for rows where: (columnB contains either zz or mm...)
columnA = x AND ( columnB = zz OR columnB LIKE '%mm%' )
I am trying following which is not giving correct results.
$val = 'mm';
$my_rs = $schema-resultset('Mytable')-search({
columnA = 'x',
columnB = { like = 'zz' },
columnB = { like = '%'.$val.'%' }
});
Nobody explained why your original attempt did not work. What you feed
to search() is a plain hash, with keys columnA and columnB. Supplying
two values for the same hash-key has a well defined behavior - it only
takes the last one. Hence the extra clarifications as shown in earlier
replies.
Cheers!
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Caching a resultset?
On Thu, Jan 17, 2013 at 01:06:43AM +1100, Peter Rabbitson wrote: On Wed, Jan 16, 2013 at 02:47:19PM +0100, Alexander Hartmaier wrote: I had NO idea that's already possible, thanks for the great pointers! Do we want to include that in the Cookbook? There is no such thing as too much documentation. Please go for it. Also see if you can do something about [1] while you are at it would be great. [1] http://lists.scsys.co.uk/pipermail/dbix-class/2013-January/011040.html Any luck with that? :) ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Prefetch and join on different tables
Hi! Thank you for your answer. Unfortunately neither of the 2 possibilities
work. In both cases I get the same error:
[error] DBIx::Class::Row::get_column(): No such column 'h1'
2013/1/18 Patrick Meidl pme...@ist.ac.at
On Fri, Jan 18 2013, mpm mpmagari...@gmail.com wrote:
push @{ $attr-{join} }, 'mt';
push @{ $attr-{join} }, 'mc';
my $rs1 = $c-model('weight::Mdata')-search( $where, $attr,{prefetch =
'mc'});
I think your curly braces are misplaced. this should do what you want:
my $rs1 = $c-model('weight::Mdata')-search($where,
{ %$attr, prefetch = 'mc' });
or alternatively:
push @{ $attr-{join} }, 'mt';
push @{ $attr-{join} }, 'mc';
$attr-{prefetch} = 'mc';
my $rs1 = $c-model('weight::Mdata')-search($where, $attr);
HTH
patrick
--
Patrick Meidl, Mag.
Senior Expert Software Engineering
IST - Institute of Science and Technology Austria
Am Campus 1
A-3400 Klosterneuburg, Austria
R 03.EG.003
T +43 2243 9000 1063
E pme...@ist.ac.at
W http://www.ist.ac.at/
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] exception_action fail after DBIC upgrade
On Mon, Jan 21, 2013 at 09:26:24PM +0400, Konstantin A. Pustovalov wrote: On 20.01.2013 23:11, Peter Rabbitson wrote: On Mon, Dec 10, 2012 at 08:09:48PM +1100, Peter Rabbitson wrote: On Mon, Dec 10, 2012 at 12:30:52PM +0400, Konstantin A. Pustovalov wrote: Hello list! I'm using exception_action feature. Some of my tests fail after upgrading 0.08196 - 0.08204 I have reduced test case to the following: http://paste.scsys.co.uk/217862 exception_action is never get called in my setup. Am I doing something wrong or is it intended behavior? Unintended breakage due to lack of tests - so when a refactor broke it things went unnoticed. Can you please add several tests like the one attached to t/34exception_action.t[1], and we will fix it for the next version. Thanks! [1] https://github.com/dbsrgits/dbix-class/blob/master/t/34exception_action.t Hi! Any word on these tests I asked you about? Cheers! Hi! Sorry for being silent. Loong russian holidays, you know ) But I have investigated a little. DBIC seems to not notice the exception if it (exeption) stringifies to zero length string. That was OK for my code since all error info was coded into exception type. Till DBIC upgrade. The fix was to add some message to all exceptions. Should I still pull test to fixate 0.08196 behavior? Yes please. While this is ultimately a problem with your code (an exception may *never* stringify to , read `perldoc -f eval` for the exact reason why), I still want to get DBIC to handle it correctly. Cheers ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Prefetch and join on different tables
I really don't understand why this happens...
If the search is:
my $rs1 = $c-model('weight::Mdata')-search($where,$attr,{prefetch =
'mc'},{join ='mt'});
The DBIC TRACE shows that the join occurs between mdata and mt, and I get
the following error:
[error] DBIx::Class::ResultSet::next(): DBI Exception: DBD::mysql::st
execute failed: Unknown column 'mc.h1' in 'where clause'
Inversely, if the search is:
my $rs1 = $c-model('weight::Mdata')-search($where,$attr,{join
='mt'},{prefetch = 'mc'});
The DBIC TRACE shows that the join (and the prefetch) occurs between mdata
and mc, and I get the following error:
[error] DBIx::Class::ResultSet::next(): DBI Exception: DBD::mysql::st
execute failed: Unknown column 'mt.n_C' in 'where clause'
It seems as the prefetch 'erases' the join and viceversa...
2013/1/21 Paula mpmagari...@gmail.com
Hi! Thank you for your answer. Unfortunately neither of the 2
possibilities work. In both cases I get the same error:
[error] DBIx::Class::Row::get_column(): No such column 'h1'
2013/1/18 Patrick Meidl pme...@ist.ac.at
On Fri, Jan 18 2013, mpm mpmagari...@gmail.com wrote:
push @{ $attr-{join} }, 'mt';
push @{ $attr-{join} }, 'mc';
my $rs1 = $c-model('weight::Mdata')-search( $where, $attr,{prefetch =
'mc'});
I think your curly braces are misplaced. this should do what you want:
my $rs1 = $c-model('weight::Mdata')-search($where,
{ %$attr, prefetch = 'mc' });
or alternatively:
push @{ $attr-{join} }, 'mt';
push @{ $attr-{join} }, 'mc';
$attr-{prefetch} = 'mc';
my $rs1 = $c-model('weight::Mdata')-search($where, $attr);
HTH
patrick
--
Patrick Meidl, Mag.
Senior Expert Software Engineering
IST - Institute of Science and Technology Austria
Am Campus 1
A-3400 Klosterneuburg, Austria
R 03.EG.003
T +43 2243 9000 1063
E pme...@ist.ac.at
W http://www.ist.ac.at/
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
[Dbix-class] Re: Prefetch and join on different tables
Paula mpmagari...@gmail.com writes:
I really don't understand why this happens...
If the search is:
my $rs1 = $c-model('weight::Mdata')-search($where,$attr,{prefetch =
'mc'},{join ='mt'});
The DBIC TRACE shows that the join occurs between mdata and mt, and I get
the following error:
[error] DBIx::Class::ResultSet::next(): DBI Exception: DBD::mysql::st
execute failed: Unknown column 'mc.h1' in 'where clause'
All the attributes need to go in the second argument to -search:
-search($where, { %{$attr}, prefetch = 'mc', join = 'mt' });
Note that this will override any prefetch or join supplied in $attr.
Inversely, if the search is:
my $rs1 = $c-model('weight::Mdata')-search($where,$attr,{join
='mt'},{prefetch = 'mc'});
The DBIC TRACE shows that the join (and the prefetch) occurs between mdata
and mc, and I get the following error:
[error] DBIx::Class::ResultSet::next(): DBI Exception: DBD::mysql::st
execute failed: Unknown column 'mt.n_C' in 'where clause'
It seems as the prefetch 'erases' the join and viceversa...
2013/1/21 Paula mpmagari...@gmail.com
Hi! Thank you for your answer. Unfortunately neither of the 2
possibilities work. In both cases I get the same error:
[error] DBIx::Class::Row::get_column(): No such column 'h1'
2013/1/18 Patrick Meidl pme...@ist.ac.at
On Fri, Jan 18 2013, mpm mpmagari...@gmail.com wrote:
push @{ $attr-{join} }, 'mt';
push @{ $attr-{join} }, 'mc';
my $rs1 = $c-model('weight::Mdata')-search( $where, $attr,{prefetch =
'mc'});
I think your curly braces are misplaced. this should do what you want:
my $rs1 = $c-model('weight::Mdata')-search($where,
{ %$attr, prefetch = 'mc' });
or alternatively:
push @{ $attr-{join} }, 'mt';
push @{ $attr-{join} }, 'mc';
$attr-{prefetch} = 'mc';
my $rs1 = $c-model('weight::Mdata')-search($where, $attr);
HTH
patrick
--
Patrick Meidl, Mag.
Senior Expert Software Engineering
IST - Institute of Science and Technology Austria
Am Campus 1
A-3400 Klosterneuburg, Austria
R 03.EG.003
T +43 2243 9000 1063
E pme...@ist.ac.at
W http://www.ist.ac.at/
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive:
http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
--
A disappointingly low fraction of the human race is,
at any given time, on fire. - Stig Sandbeck Mathisen
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Re: Prefetch and join on different tables
2013/1/21 Dagfinn Ilmari Mannsåker ilm...@ilmari.org
-search($where, { %{$attr}, prefetch = 'mc', join = 'mt' });
I have just tried that, and again I get:
[error] DBIx::Class::Row::get_column(): No such column 'h1'
It seems the order is important, the last argument (prefetch or join) is
the only one that is executed...
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] exception_action fail after DBIC upgrade
Hi! Sorry for being silent. Loong russian holidays, you know ) But I have investigated a little. DBIC seems to not notice the exception if it (exeption) stringifies to zero length string. That was OK for my code since all error info was coded into exception type. Till DBIC upgrade. The fix was to add some message to all exceptions. Should I still pull test to fixate 0.08196 behavior? Yes please. While this is ultimately a problem with your code (an exception may *never* stringify to , read `perldoc -f eval` for the exact reason why), I still want to get DBIC to handle it correctly. Cheers If you mean this snip from the doc: If there was no error, $@ is guaranteed to be the empty string then this is not my case. Object which stringifies to empty string is definitely not the same as empty string (the former is true value while latter is not). Pull request sent. ___ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] exception_action fail after DBIC upgrade
On Tue, Jan 22, 2013 at 01:23:51AM +0400, Konstantin A. Pustovalov wrote:
Hi!
Sorry for being silent. Loong russian holidays, you know )
But I have investigated a little. DBIC seems to not notice the
exception if it (exeption) stringifies to zero length string. That
was OK for my code since all error info was coded into exception
type. Till DBIC upgrade. The fix was to add some message to all
exceptions. Should I still pull test to fixate 0.08196 behavior?
Yes please. While this is ultimately a problem with your code (an
exception may *never* stringify to , read `perldoc -f eval` for the
exact reason why), I still want to get DBIC to handle it correctly.
Cheers
If you mean this snip from the doc:
If there was no error, $@ is guaranteed to be the empty string
then this is not my case.
It actually is, you are just not looking at it from the viewpoint of
an external developer.
Object which stringifies to empty string
is definitely not the same as empty string (the former is true value
while latter is not).
Yes, except that it is a flawed assumption that folks check for
truthiness of $@. In fact any code that does this (if ($@) { ... }) is
arguably wrong. The only correct way to check for an exception is (if
($@ eq '') { ... } ) after an eval just took place. At which point you
are landing straight into the trap that your exception objects are. Here
is a little nonexhaustive search to convince you that the problem is not
just academical [1]
Which brings me to your pull request - it can't be accepted as-is. The
case of a ''-stringifying object is not business as usual. Hence the
test you added needs to be tweaked to expect a *dfferent* exception,
saying something like Your exception thrown blah blah stringifies to
the empty string - this is not ok, fix your exception library. Original
exception was Foo::Bar=HASH(0x9bb77d0). Or something like that.
[1] http://is.gd/RRXRpf
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Re: Prefetch and join on different tables
Good afternoon,
On 21/01/13 at 5:39 PM -0300, Paula mpmagari...@gmail.com wrote:
2013/1/21 Dagfinn Ilmari Mannsåker ilm...@ilmari.org
-search($where, { %{$attr}, prefetch = 'mc', join = 'mt' });
I have just tried that, and again I get:
[error] DBIx::Class::Row::get_column(): No such column 'h1'
It seems the order is important, the last argument (prefetch or join) is
the only one that is executed...
Correct, since standard perl behaviour says there is only one key in a hash.
Assuming $attr-{join} is currently a string (if already arrayref then just
push onto it):
$attr-{join} = [$attr-{join}, 'mt'];
$attr-{prefetch} = [$attr-{prefetch}, 'mc'];
search($where, $attr});
Charlie
--
Ꮚ Charlie Garrison ♊ garri...@zeta.org.au
O ascii ribbon campaign - stop html mail - www.asciiribbon.org
〠 http://www.ietf.org/rfc/rfc1855.txt
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
Re: [Dbix-class] Re: help needed in forming a 'and' and 'or' select query using DBIx::Class
Thanks to all for help and explanation as to why so.
I liked the below solution the best. thx again.
From: Dagfinn Ilmari Mannsåker ilm...@ilmari.org
To: dbix-class@lists.scsys.co.uk
Sent: Monday, January 21, 2013 4:34 AM
Subject: [Dbix-class] Re: help needed in forming a 'and' and 'or' select query
using DBIx::Class
Michele Beltrame m...@cattlegrid.info writes:
Hello!
columnA = x AND ( columnB = zz OR columnB LIKE '%mm%' )
It should be like this:
$val = 'mm';
$my_rs = $schema-resultset('Mytable')-search({
columnA = 'x',
-or = [
columnB = 'zz',
columnB = { like = '%'.$val.'%' }
],
});
Or a slightly more concise version:
my $rs = $schema-resultset('MyTable')-search({
columnA = 'x',
columnB = [ 'zz', { like = '%'.$val'%' } ],
});
--
The surreality of the universe tends towards a maximum -- Skud's Law
Never formulate a law or axiom that you're not prepared to live with
the consequences of. -- Skud's Meta-Law
___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk___
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
