Re: [Dbix-class] DBIC_TRACE query OK but code throws an exception (many-to-many relationship)
Denis BAURAIN wrote:
Hi,
I am new to DBIC and to this list. Thus, I apologise in advance if my
first question has already been asked a thousand times.
I am struggling to convert an existing SQL query into DBIC parlance...
Oddly, DBIC_TRACE shows that the expected query is indeed built but my
Perl code throws an error. I would appreciate any enlightenment about
what I am necessarily doing wrong... I provide the minimal context below.
my @matures = $schema-resultset('miRNA')
-search( {'species.name' = 'Homo sapiens'}, {
'join' = ['species', {'miRNA_matures' = 'mature'}],
'distinct' = 1,
'columns' = [qw/mature.mature_acc mature.mature_name/]
})-all
;
Here's the output with the error. If I fill the '?' placeholder and run
the traced query against the database, it gives me what I want (see below).
No such relationship 'mature' on miRNA at /Users/denis/perl5/perlbrew/
Sorry for the late reply, I thought someone else replied already. The problem
here is that columns == select/as specifications, so what you wrote ends
up being:
as = [qw/mature.mature_acc mature.mature_name/]
which translates as:
$main_obj-mature-mature_acc (foo)
$main_obj-mature-mature_name (bar)
What you needed instead was:
as = [qw/miRNA_matures,mature.mature_acc/]
which would correctly populate
$main_obj-miRNA_matures-slice(whichever relation this
is)-mature-mature_acc('foo')
I may try to add an early-barf for this but it isn't very easy. Will ponder.
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] Persistent connections with CGI::Application::Plugin::DBIx::Class
I have two questions about using CGI::Application::Plugin::DBIx::Class for
CRUD operations in a CGI::Application base app.
1. If I have setup the connection with $self-dbic_config({schema =3D
MyApp::Schema-connect(@connection_data)}) will $app-schema give me the
full DBIx::Class schema instance, or will I get a restricted instance on
which I can only call methods defined in the CAP::DBIx::Class documentation?
2. I am running my CGI::Application subclass under mod_perl. In this case
with the connection be persistent? Or should I use Apache::DBI to ensure
that it is.
If I recall correctly CAP::DBIC just associates the schema to the CA
instance, along with some commonly used functions. So you should have
access to everything.
I would not use Apache::DBI, because DBIC already manages its own
connections, and they probably don't mix well. I believe DBIC makes a
connection per-process, and keeps it persistent reconnecting only if the
connection died, etc...
___
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] Loading ResultSource's abit like a trait
On Mon, Nov 8, 2010 at 3:03 AM, Benjamin Martin
benjamin.mar...@ims-evolve.com wrote:
Hello all,
I have a dbix::class schema with quite a few resultsource files.
The schema is used by a few different apps.
Typically only a few a of the resultsource files need to be loaded as the
app using the schema is only touching a few tables.. but in other
applications alot more of the resultsource files are needed.
The start up time and mem foot print is alot larger than it could be for
the apps only using part of the schema .. so ...
My question is, is there is a way to instanciate a schema but only loading
a few of the resultsource files?
I was thinking perhaps I could do something that loads resultsource files
simlar to traits but am not sure if this is advisable or if there is already
something that does this?
Many thanks for any comments/advise/links you have that might help.
Cheers,
-Ben
We had a similar situation where we have something like 600 tables for a
bunch of reporting tables, so we just load those on demand. This is how we
did it (a method in our schema):
sub load_report {
my $self = shift;
my $report_num = shift or die 'report_num is required for load_report';
return if (first { $_ eq Rpt$report_num } $self-sources); # don't load
again if it's loaded already
eval require MyApp::Schema::NonDefaultResult::rpt_$report_num;
$self-register_class(Rpt$report_num,
MyApp::Schema::NonDefaultResult::rpt_$report_num);
return $self;
}
Note that if you are doing this for *all* of your resultsets you might as
well just override the resultset method itself and have it load the other
classes lazily.
Also note that I put things in NonDefaultResult; that's because we are using
load_namespaces(). If you really do want to load all of your classes
lazily I recommend *not* doing load_namespaces and putting the classes in
the standard location.
--
fREW Schmidt
http://blog.afoolishmanifesto.com
___
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] Chained resultsets and WHERE operators
It appears that you are *not* chaining these resultsets...
If you first do...
# Narrow the search down if the start date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $start_date },
end_date = { '=', $start_date },
}) if $start_date;
then in order to 'chain' you must...
$date_ranges = $date_ranges-search({...
You overwrote your resultset when you...
# Narrow the search down if the end date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $end_date },
end_date = { '=', $end_date },
}) if $end_date;
On 11/9/2010 8:36 AM, Mike Raynham wrote:
Hi,
I am probably missing something obvious here, but have been unable to
find a clean solution to the following problem, and I'm hoping that
your expert knowledge will come to the rescue.
I have created a query which determines if a given date range exists
within date ranges stored in the database:
###
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) OR
# (start_date = $end_date AND end_date = $end_date)
my $date_ranges = $rs-search({
-or = [
-and = [
start_date = { '=', $start_date },
end_date = { '=', $start_date },
],
-and = [
start_date = { '=', $end_date },
end_date = { '=', $end_date },
],
],
});
###
This works correctly, but I would like to be able to run the search
for open ended date ranges - that is, if only the start or only the
end date is supplied. I have attempted to do this by chaining search
queries, as follows:
###
# Create a search.
my $date_ranges = $rs-search({});
# Narrow the search down if the start date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $start_date },
end_date = { '=', $start_date },
}) if $start_date;
# Narrow the search down if the end date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $end_date },
end_date = { '=', $end_date },
}) if $end_date;
###
This works correctly when either the start or end date is supplied.
However, when both dates are supplied, the two WHERE conditions are
chained together with an AND:
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) AND
# (start_date = $end_date AND end_date = $end_date)
Instead, I need them to be chained together with an OR:
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) OR
# (start_date = $end_date AND end_date = $end_date)
Is there a way that I can cause the two queries to be chained together
with an OR instead of an AND?
At the moment, I have got round the problem by using the non-chained
query at the top of this post, and specifying out-of-range defaults
for dates that have not been supplied.
Regards,
Mike
___
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] Chained resultsets and WHERE operators
Hi,
I am probably missing something obvious here, but have been unable to
find a clean solution to the following problem, and I'm hoping that your
expert knowledge will come to the rescue.
I have created a query which determines if a given date range exists
within date ranges stored in the database:
###
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) OR
# (start_date = $end_date AND end_date = $end_date)
my $date_ranges = $rs-search({
-or = [
-and = [
start_date = { '=', $start_date },
end_date = { '=', $start_date },
],
-and = [
start_date = { '=', $end_date },
end_date = { '=', $end_date },
],
],
});
###
This works correctly, but I would like to be able to run the search for
open ended date ranges - that is, if only the start or only the end date
is supplied. I have attempted to do this by chaining search queries, as
follows:
###
# Create a search.
my $date_ranges = $rs-search({});
# Narrow the search down if the start date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $start_date },
end_date = { '=', $start_date },
}) if $start_date;
# Narrow the search down if the end date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $end_date },
end_date = { '=', $end_date },
}) if $end_date;
###
This works correctly when either the start or end date is supplied.
However, when both dates are supplied, the two WHERE conditions are
chained together with an AND:
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) AND
# (start_date = $end_date AND end_date = $end_date)
Instead, I need them to be chained together with an OR:
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) OR
# (start_date = $end_date AND end_date = $end_date)
Is there a way that I can cause the two queries to be chained together
with an OR instead of an AND?
At the moment, I have got round the problem by using the non-chained
query at the top of this post, and specifying out-of-range defaults for
dates that have not been supplied.
Regards,
Mike
___
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] Chained resultsets and WHERE operators
On 09/11/10 13:49, Steve wrote:
It appears that you are *not* chaining these resultsets...
If you first do...
# Narrow the search down if the start date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $start_date },
end_date = { '=', $start_date },
}) if $start_date;
then in order to 'chain' you must...
$date_ranges = $date_ranges-search({...
You overwrote your resultset when you...
# Narrow the search down if the end date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $end_date },
end_date = { '=', $end_date },
}) if $end_date;
Sorry, that was my mistake when reducing my original code down for the
email. You are right - it should have read:
###
# Create a search.
my $date_ranges = $rs-search({});
# Narrow the search down if the start date is supplied.
$date_ranges = $date_ranges-search({
start_date = { '=', $start_date },
end_date = { '=', $start_date },
}) if $start_date;
# Narrow the search down if the end date is supplied.
$date_ranges = $date_ranges-search({
start_date = { '=', $end_date },
end_date = { '=', $end_date },
}) if $end_date;
###
___
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] Chained resultsets and WHERE operators
On Tue, Nov 9, 2010 at 8:36 AM, Mike Raynham catal...@mikeraynham.co.ukwrote:
I have created a query which determines if a given date range exists within
date ranges stored in the database:
###
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) OR
# (start_date = $end_date AND end_date = $end_date)
my $date_ranges = $rs-search({
-or = [
-and = [
start_date = { '=', $start_date },
end_date = { '=', $start_date },
],
-and = [
start_date = { '=', $end_date },
end_date = { '=', $end_date },
],
],
});
Is there a way that I can cause the two queries to be chained together with
an OR instead of an AND?
At the moment, I have got round the problem by using the non-chained query
at the top of this post, and specifying out-of-range defaults for dates that
have not been supplied.
I don't think there is a way to merge search conditions with an OR when
chaining resultsets. I would suggest doing something like this:
my $date_ranges = $rs-search({
-or = [
$start_date
? (-and = [
start_date = { '=', $start_date },
end_date = { '=', $start_date },
])
: (),
$end_date
? (-and = [
start_date = { '=', $end_date },
end_date = { '=', $end_date },
])
: (),
],
});
Ronald
___
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] Chained resultsets and WHERE operators
On 09/11/10 15:02, Peter Rabbitson wrote:
Ronald J Kimball wrote:
On Tue, Nov 9, 2010 at 8:36 AM, Mike Raynham
catal...@mikeraynham.co.uk mailto:catal...@mikeraynham.co.uk wrote:
I have created a query which determines if a given date range exists
within date ranges stored in the database:
###
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) OR
# (start_date = $end_date AND end_date = $end_date)
my $date_ranges = $rs-search({
-or = [
-and = [
start_date = { '=', $start_date },
end_date = { '=', $start_date },
],
-and = [
start_date = { '=', $end_date },
end_date = { '=', $end_date },
],
],
});
Is there a way that I can cause the two queries to be chained
together with an OR instead of an AND?
At the moment, I have got round the problem by using the non-chained
query at the top of this post, and specifying out-of-range defaults
for dates that have not been supplied.
I don't think there is a way to merge search conditions with an OR
when chaining resultsets. I would suggest doing something like this:
This is correct. Chaining implies AND ing (tightening the condition more
and more)
Okay, thanks.
my $date_ranges = $rs-search({
-or = [
$start_date
? (-and = [
start_date = { '=', $start_date },
end_date = { '=', $start_date },
])
: (),
$end_date
? (-and = [
start_date = { '=', $end_date },
end_date = { '=', $end_date },
])
: (),
],
});
The first argument to search() is any valid SQLA structure, thus the above
can be as simple as:
$rs-search([
$start_date
? { start_date = { '=', $start_date }, end_date = { '=', $start_date
} }
: ()
,
$end_date =
? { start_date = { '=', $end_date }, end_date = { '=', $end_date }, }
: ()
,
]);
Ronald, Peter: Thank you both for your input. I like the boiled-down
solution, and will go with that.
Also I think the = = signs are wrong there somewhere
Yes, it confused me for a while :-)
The query returns any rows where *either* the supplied start or end
dates are within the start and end date ranges held in 'my_table'. That
is, given a date range it checks the table to see if that date range
overlaps any of the rows in 'my_table'.
I have tested it, and it returns the results I would expect. I think
the confusion comes from the order in which the WHERE expression is
produced. If I were to write the SQL myself, without DBIx, I'd probably
swap around the column names and supplied dates, like this:
SELECT * FROM my_table WHERE
($start_date = start_date AND $start_date = end_date) OR
($end_date = start_date AND $end_date = end_date)
That way makes more sense to me, but I don't think I can write it like
that using DBIx (unless I opt for creating SQL directly).
As you will see, it does the same thing as the DBIx generated query -
it's just reversed:
SELECT * FROM my_table WHERE
(start_date = $start_date AND end_date = $start_date) OR
(start_date = $end_date AND end_date = $end_date)
Regards,
Mike
___
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] Chained resultsets and WHERE operators
Mike Raynham wrote: I have tested it, and it returns the results I would expect. I think the confusion comes from the order in which the WHERE expression is produced. If I were to write the SQL myself, without DBIx, I'd probably It's DBIx::Class. DBIx is a namespace with hundreds of modules in it, it is not fair to the respective authors to default DBIx to DBIx::Class. If you want to abbreviate DBIC is common. swap around the column names and supplied dates, like this: SELECT * FROM my_table WHERE ($start_date = start_date AND $start_date = end_date) OR ($end_date = start_date AND $end_date = end_date) That way makes more sense to me, but I don't think I can write it like that using DBIx (unless I opt for creating SQL directly). I don't see why not... ? Explain what do you perceive the holdup would be. ___ 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] Chained resultsets and WHERE operators
On 09/11/10 16:14, Peter Rabbitson wrote:
Mike Raynham wrote:
I have tested it, and it returns the results I would expect. I think
the confusion comes from the order in which the WHERE expression is
produced. If I were to write the SQL myself, without DBIx, I'd probably
It's DBIx::Class. DBIx is a namespace with hundreds of modules in it,
it is not fair to the respective authors to default DBIx to DBIx::Class.
If you want to abbreviate DBIC is common.
Please accept my apologies. I greatly appreciate all the work that has
gone into DBIx::Class and all the other modules in CPAN. I didn't mean
to cause offence and will use DBIC or DBIx::Class in future.
swap around the column names and supplied dates, like this:
SELECT * FROM my_table WHERE
($start_date = start_date AND $start_date = end_date) OR
($end_date = start_date AND $end_date = end_date)
That way makes more sense to me, but I don't think I can write it like
that using DBIx (unless I opt for creating SQL directly).
I don't see why not... ? Explain what do you perceive the holdup would
be.
It's not that I thought there would be a holdup, I just don't know how
to do it, or even if it is possible. I'm fairly new to perl, and very
new to the DBIx::Class, so have a lot to learn.
Given:
$rs-search({
start_date = { '=', $start_date }
});
..the resulting SQL is along the lines of:
SELECT * FROM my_table WHERE start_date = $start_date
I didn't realised that it was possible to switch the variable and table
column around to give this:
SELECT * FROM my_table WHERE $start_date = start_date
As you said that you didn't see why it wasn't possible, I've had another
look at the DBIx::Class::Manual::FAQ under .. search with an SQL
function on the left hand side?. The -nest modifier may be the
answer, but I've not tried it yet.
___
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] Chained resultsets and WHERE operators
Mike Raynham wrote:
On 09/11/10 16:14, Peter Rabbitson wrote:
Mike Raynham wrote:
I have tested it, and it returns the results I would expect. I think
the confusion comes from the order in which the WHERE expression is
produced. If I were to write the SQL myself, without DBIx, I'd probably
It's DBIx::Class. DBIx is a namespace with hundreds of modules in it,
it is not fair to the respective authors to default DBIx to DBIx::Class.
If you want to abbreviate DBIC is common.
Please accept my apologies. I greatly appreciate all the work that has
gone into DBIx::Class and all the other modules in CPAN. I didn't mean
to cause offence and will use DBIC or DBIx::Class in future.
swap around the column names and supplied dates, like this:
SELECT * FROM my_table WHERE
($start_date = start_date AND $start_date = end_date) OR
($end_date = start_date AND $end_date = end_date)
That way makes more sense to me, but I don't think I can write it like
that using DBIx (unless I opt for creating SQL directly).
I don't see why not... ? Explain what do you perceive the holdup would
be.
It's not that I thought there would be a holdup, I just don't know how
to do it, or even if it is possible. I'm fairly new to perl, and very
new to the DBIx::Class, so have a lot to learn.
Given:
$rs-search({
start_date = { '=', $start_date }
});
..the resulting SQL is along the lines of:
SELECT * FROM my_table WHERE start_date = $start_date
I didn't realised that it was possible to switch the variable and table
column around to give this:
SELECT * FROM my_table WHERE $start_date = start_date
As you said that you didn't see why it wasn't possible, I've had another
look at the DBIx::Class::Manual::FAQ under .. search with an SQL
function on the left hand side?. The -nest modifier may be the
answer, but I've not tried it yet.
-nest...? what...?
I still don't understand how this is different from anything you've showed
us in the thread so far. You said you want to do:
WHERE
($start_date = start_date AND $start_date = end_date)
OR
($end_date = start_date AND $end_date = end_date)
In SQLA it is assumed that the lhs is a column and the rhs is a bind value
(this is a *generalization*, there are all kinds of exceptions, but as a
generalization it will suffice)
So we rewrite this as:
(start_date = $start_date AND end_date = $start_date)
OR
(start_date = $end_date AND end_date = $end_date)
So then it boils down to:
[
{ start_date = { '=', $start_date }, end_date = { '=', $start_date } },
{ start_date = { '=', $end_date }, end_date = { '=', $end_date } },
]
That's it. No -nest, no braindamage.
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] Chained resultsets and WHERE operators
On 09/11/10 22:26, Peter Rabbitson wrote:
-nest...? what...?
I still don't understand how this is different from anything you've showed
us in the thread so far. You said you want to do:
WHERE
($start_date = start_date AND $start_date = end_date)
OR
($end_date = start_date AND $end_date = end_date)
In SQLA it is assumed that the lhs is a column and the rhs is a bind value
(this is a *generalization*, there are all kinds of exceptions, but as a
generalization it will suffice)
So we rewrite this as:
(start_date = $start_date AND end_date = $start_date)
OR
(start_date = $end_date AND end_date = $end_date)
So then it boils down to:
[
{ start_date = { '=', $start_date }, end_date = { '=', $start_date } },
{ start_date = { '=', $end_date }, end_date = { '=', $end_date } },
]
That's it. No -nest, no braindamage.
Cheers
Hi Peter,
Thank you for you input, and your solution. I appear to have created
some confusion, so please let me attempt to clarify things.
My original question was about chaining with an OR instead of an AND.
You pointed out that:
Chaining implies AND ing (tightening the condition more
and more)
Ronald J Kimball provided an elegant solution which you kindly
simplified. I thanked you both for this, and said that I liked the
solution and that I would use it.
I then mentioned that if I were to write the query manually, I would
probably swap the column and bind values around. This is simply because
that way the meaning looks a little clearer to me. I fully understand
that in this case, it doesn't affect the query in any way. I also
pointed out that it would do exactly the same thing as the original query.
I didn't think that it was possible (at least not easily or sensibly) to
swap the column and bind values around when generating a search with
DBIx::Class::Resultset, and was perfectly happy to leave it at that.
However, your response to this was:
I don't see why not... ? Explain what do you perceive the holdup would
be.
From that, I assumed that maybe there is a way to easily swap the
column and bind values around. I checked the DBIx::Class::Manual::FAQ,
and found a mention of -nest, which appeared to offer a way of doing it.
I didn't try it. I was, and still am, happy to restructure my
original query to incorporate your solution.
Regards,
Mike
___
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
