Re: [Dbix-class] Error on sub class
On 01/16/2015 11:41 AM, Mitchell Elutovich wrote: $perl -v This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi $ perl -MDBIx::Class -le 'print $DBIx::Class::VERSION' 0.082810 Anyone know why I might be now running into this? I'm not sure how long this problem has existed and I thought I was originally using the sub class ok. This is a new check that was introduced during the 0.0828xx series. It was introduced due to a lot of abuse-cases of the relationship system. Nevertheless in this case it seems like a false positive - i.e. your usage seems valid. Can you please disable the exception-check in question in DBIx::Class::ResultSource::_resolve_relationship_condition, and tell me if everything else seems to behave in a sensible manner? If this is the case - I would have to remove this check going forward. ___ 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] Error when using distinct = 1 with select = ...
On 01/12/2015 07:54 AM, Octavian Rasnita wrote:
Hello,
I tried to use the following select query, but it gives an error:
use TB::Schema;
$ENV{DBIC_TRACE}++;
my $schema = TB::Schema-connect( dbi:mysql:intranet, root );
my $rs = $schema-resultset('Performance')-search( {}, {
select = [ { date = 'date_time' } ],
as = [ 'date' ],
distinct = 1,
} );
$rs-all;
This gives the following result:
SELECT DATE( date_time ) FROM performance me GROUP BY :
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st
execute failed: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '' at line 1 [for Statement SELECT DATE( date_time ) FROM
performance me GROUP BY ] at D:\test_dbic\test.pl line 18
There are two bugs in place here:
* DBIC does not check for an empty group_by properly (as seen in your case)
* distinct = 1 itself behaves correctly, but is not properly documented
(argh!!!). The gist is all columns are considered *except* for functions
Both will be rectified in the next stable version.
___
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] update and join
On 01/06/2015 09:58 AM, RAPPAZ Francois wrote:
Hi Peter,
As I said,
$s-resultset('Ddref')-search(
{ 'RefUser.iduser' = 3 },
{ join = 'RefUser' }
)-update({ id_credit = 22 });
You are missing that
$s-resultset('Ddref')-search(
{ 'RefUser.iduser' = 3 },
{ join = 'RefUser' }
);
Does not return you a synthetic resultset combining both Ddref and the
Dduser sources. The result of this -search is *still* a resultset
pointing to Ddref alone. This is a core design consideration within DBIC
- a resultset only points to one RDBMS-side source. Therefore any
-delete/-update/-create operations work on that source alone, and
nothing else.
The only way to update a field in a source *related* to the source you
started from is to switch the current scope via search_related:
$s-resultset('Ddref')
-search_related('RefUser')
-search({ 'RefUser.iduser' = 3 })
-update({id_credit = 22 });
Is this more helpful?
___
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] Fixing my relationships to get working queries
Peter == Peter Rabbitson rabbit+d...@rabbit.us writes:
Peter On 01/13/2015 03:33 AM, John Stoffel wrote:
# search in Carey
my @r = $schema-resultset('Name')-search( { full_name =
{ regexp = '[[::]]'.$query.'[[::]]' }
},
{
order_by = { -asc = 'full_name' },
prefetch = { 'account' = 'boxfolder' },
rows = $limit,
});
return @r;
And I don't get back consistent the info I expect. Sometimes it works
and I get what I think is the right info, but other times for other
queries it doesn't give me back what I want.
Peter You need to expand on what precisely isn't as expected. That
Peter is - from your query above I can see DBIC generating the SQL to
Peter satisfy exactly what you asked for. So the disconnect is in you
Peter ot fully understanding the way you formulated the query to
Peter DBIC itself.
Sorry, I'm expecting to get back consistent results. I've actually
given up trying to make multi-step join or prefetch work for me,
because I was running out of time. So I ended up de-normalizing my
data.
Just to refresh the conversation, I have a table with the following
two level relationship:
Names - has_many - Accounts - has_one - Boxfolder
Where many different accounts can share a Boxfolder row. Basic
stuff.
So in the end I simply copied the columns from Boxfolder into Accounts
and then copied over the data. It's only 20,000 rows, so it's nothing
huge and it now works for me.
Peter Please expand on this so I can answer your question in a manner that
Peter will help you generally in the future.
I've been looking at the DBIx::Class::Manual::Cookbook at the
Multi-Step prefetch but since the example doesn't give the
relationships, it's hard for me to mentally map what I'm reading in
the example code to what I have. This is probably my biggest
complaint of all the examples, they just assume to much knowledge.
Anyway, I have the following classes, with Name being the only class I
search, using the full_name column.
Name.pm:
package Carey::Schema::Result::Name;
use base 'DBIx::Class::Core';
__PACKAGE__-table(names);
__PACKAGE__-add_columns(
name_id,
{ data_type = integer, is_auto_increment = 1, is_nullable = 0
},
full_name,
{ data_type = varchar, is_nullable = 0, size = 50 },
last_name,
{ data_type = text, is_nullable = 1 },
first_name,
{ data_type = text, is_nullable = 1 },
comments,
{ data_type = varchar, is_nullable = 1, size = 100 },
);
__PACKAGE__-set_primary_key(name_id);
__PACKAGE__-has_many('account', 'Carey::Schema::Result::Account','name_id');
Account.pm:
package Carey::Schema::Result::Account;
use base 'DBIx::Class::Core';
__PACKAGE__-table(account);
__PACKAGE__-add_columns(
account_id,
{ data_type = integer, is_auto_increment = 1, is_nullable = 0
},
account_number,
{ data_type = varchar, is_nullable = 0, size = 10 },
boxfolder_id,
{ data_type = integer, is_nullable = 0 },
name_id,
{ data_type = integer, is_nullable = 1 },
url,
{ data_type = varchar, is_nullable = 1, size = 1028 },
comments,
{ data_type = varchar, is_nullable = 1, size = 100 },
volume,
{ data_type = varchar, is_nullable = 0, size = 20 },
box,
{ data_type = varchar, is_nullable = 0, size = 10 },
folder,
{ data_type = varchar, is_nullable = 1, size = 20 },
range,
{ data_type = text, is_nullable = 1 },
comments,
{ data_type = varchar, is_nullable = 1, size = 100 },
);
__PACKAGE__-set_primary_key(account_id);
__PACKAGE__-belongs_to('name', 'Carey::Schema::Result::Name','name_id');
__PACKAGE__-has_one('boxfolder', 'Carey::Schema::Result::Boxfolder',
'boxfolder_id');
Boxfolder.pm:
package Carey::Schema::Result::Boxfolder;
use base 'DBIx::Class::Core';
__PACKAGE__-table(boxfolder);
__PACKAGE__-add_columns(
boxfolder_id,
{ data_type = integer, is_auto_increment = 1, is_nullable = 0
},
volume,
{ data_type = varchar, is_nullable = 0, size = 20 },
box,
{ data_type = varchar, is_nullable = 0, size = 10 },
folder,
{ data_type = varchar, is_nullable = 1, size = 20 },
range,
{ data_type = text, is_nullable = 1 },
comments,
{ data_type = varchar, is_nullable = 1, size = 100 },
);
__PACKAGE__-set_primary_key(boxfolder_id);
__PACKAGE__-belongs_to('account','Carey::Schema::Result::Account','boxfolder_id');
So when I run my test search script, it generates an SQL query using
TWO values passed in, both of which are the regexps I'm searching
for. Instead of getting something like this query:
mysql SELECT n.name_id,n.full_name,a.account_id,b.boxfolder_id,
mysql b.volume, b.folder, b.range,
mysql substring(a.url,118,locate('',a.url,118)-118) AS value2 FROM
mysql names as n LEFT JOIN account AS a ON n.name_id = a.name_id LEFT
mysql JOIN boxfolder AS b on a.boxfolder_id = b.boxfolder_id WHERE
mysql n.full_name REGEXP '[[::]]carpenter[[::]]';
I get this
Re: [Dbix-class] Fixing my relationships to get working queries
On 01/19/2015 06:41 PM, John Stoffel wrote:
Sorry, I'm expecting to get back consistent results.
I am not entirely sure how to interpret that... :)
I've actually
given up trying to make multi-step join or prefetch work for me,
because I was running out of time.
Sorry about that - the mailing list had issues, I only received your
emails today.
Just to refresh the conversation,
...
I get this monstrosity:
SELECT me.name_id, me.full_name, me.last_name, me.first_name,
me.comments, account.account_id, account.account_number,
account.boxfolder_id, account.name_id, account.url, account.comments,
account.volume, account.box, account.folder, account.range FROM
(SELECT me.name_id, me.full_name, me.last_name, me.first_name,
me.comments FROM names me WHERE ( full_name REGEXP ? ) ORDER BY
full_name ASC LIMIT ?) me LEFT JOIN account account ON account.name_id
= me.name_id WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC:
'[[::]]carpenter[[::]]', '10', '[[::]]carpenter[[::]]'
This (as you called it) monstrosity is there for a good reason.
However I can't really explain it without the actual code that produced
it (hint - the thing below is *not* what produced the above query - it
is missing a rows = spec)
my $rs = $schema-resultset('Name')-search({ full_name = { regexp =
'[[::]]'.$name.'[[::]]' },
},
{
prefetch = [ 'account' ],
order_by = { -asc = 'full_name'
},
});
If you have time and want to really understand what was going on - let's
start over with an actual code/query example.
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] Fixing my relationships to get working queries
On 01/19/2015 07:23 PM, John Stoffel wrote:
For my perl code, to get the same result, I was trying to use the
following perl code, which removes a bunch of setup code to make it
smaller:
my $rs = $schema-resultset('Name')-search({ full_name =
{ regexp =
'[[::]]'.$name.'[[::]]' }
},
{
prefetch = [ 'account' ],
rows = 10,
order_by = { -asc =
'full_name' },
});
Ok, so DBIC did run one statement, you are simply unhappy about the
subquery. Given you are focused on what DBIC does, the easiest way to
explain it would be for you to remove the 'rows = 10' above, and to
re-run the query and observe the trace.
Realize that the '10' applies to the amount of Names, not to the amount
of Names + unknown number of Accounts per name.
If you can come up with a more efficient way to ask a RDBMS for first N
things, and *all* their related things (a not-beforehand-known amount
for each individual thing of the N things) - please share, as it would
be of great interest to me.
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] Fixing my relationships to get working queries
Peter On 01/19/2015 06:41 PM, John Stoffel wrote:
Sorry, I'm expecting to get back consistent results.
Peter I am not entirely sure how to interpret that... :)
Me too... and I wrote it! I did see that the list had issues over the
Christmas break, so I'm sure we'll a bit behind. I've got a cold now
which is also slowing my brain down.
I've actually
given up trying to make multi-step join or prefetch work for me,
because I was running out of time.
Peter Sorry about that - the mailing list had issues, I only received your
Peter emails today.
No problem, if I can make it work properly, I'd be happy to use it
since I do think it makes more sense, but I can survive with what I've
got now I think.
Just to refresh the conversation,
...
I get this monstrosity:
SELECT me.name_id, me.full_name, me.last_name, me.first_name,
me.comments, account.account_id, account.account_number,
account.boxfolder_id, account.name_id, account.url, account.comments,
account.volume, account.box, account.folder, account.range FROM
(SELECT me.name_id, me.full_name, me.last_name, me.first_name,
me.comments FROM names me WHERE ( full_name REGEXP ? ) ORDER BY
full_name ASC LIMIT ?) me LEFT JOIN account account ON account.name_id
= me.name_id WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC:
'[[::]]carpenter[[::]]', '10', '[[::]]carpenter[[::]]'
Peter This (as you called it) monstrosity is there for a good reason.
Peter However I can't really explain it without the actual code that produced
Peter it (hint - the thing below is *not* what produced the above query - it
Peter is missing a rows = spec)
my $rs = $schema-resultset('Name')-search({ full_name = { regexp =
'[[::]]'.$name.'[[::]]' },
},
{
prefetch = [ 'account' ],
order_by = { -asc = 'full_name' },
});
Peter If you have time and want to really understand what was going on - let's
Peter start over with an actual code/query example.
Ok. Here's an example. I'm looking for all matches in full_name for
the name carpenter. In this case, I'm not limiting the rows
returned because I know I'll only get two matches. I also want to
prefetch all the data from the Account and Boxfolder tables. To do
this, I use the following mysql query:
mysql SELECT n.name_id,n.full_name, a.account_id, b.boxfolder_id,
mysql b.volume, b.folder, b.range,
mysql substring(a.url,118,locate('',a.url,118)-118) AS value2 FROM
mysql names as n LEFT JOIN account AS a ON n.name_id = a.name_id LEFT
mysql JOIN boxfolder AS b on a.boxfolder_id = b.boxfolder_id WHERE
mysql n.full_name REGEXP '[[::]]carpenter[[::]]';
+-+--++--+++---+-+
| name_id | full_name| account_id | boxfolder_id | volume |
folder | range | value2 |
+-+--++--+++---+-+
|7333 | Carpenter, John | 3176 | 423 | 35 |
fol01 | | =fol01 |
|7333 | Carpenter, John | 3177 | 423 | 35 |
fol01 | | =fol01 |
|7333 | Carpenter, John | 3178 | 423 | 35 |
fol01 | | =fol01 |
|7334 | Carpenter, John Jr. | 3179 | 106 | 16 | 1
| 6003-6128 | =b08f01 |
|7334 | Carpenter, John Jr. | 3180 | 112 | 16 | 7
| 6780-6806 | =b08f07 |
|7334 | Carpenter, John Jr. | 3181 | 122 | 17 | 2
| 6999-7125 | =b09f02 |
|7334 | Carpenter, John Jr. | 3182 | 122 | 17 | 2
| 6999-7125 | =b09f02 |
+-+--++--+++---+-+
The substring stuff is just because the URL is long and mostly
redundant. I didn't enter the data, so I'm just working around it in
this example.
For my perl code, to get the same result, I was trying to use the
following perl code, which removes a bunch of setup code to make it
smaller:
my $rs = $schema-resultset('Name')-search({ full_name =
{ regexp =
'[[::]]'.$name.'[[::]]' }
},
{
prefetch = [ 'account' ],
rows = 10,
order_by = { -asc =
'full_name' },
});
$schema-storage-debug(1);
my @r = $rs-all;
foreach my $r (@r) {
print Full Name: , $r-full_name, (, $r-name_id, )\n;
foreach my $a ($r-account()) {
print account_id=, $a-account_id();
print boxfolder_id=,$a-boxfolder_id(), ;
my $t = $a-url();
$t =~ m/value2=(\w+)\/;
print URL: $1;
$vol =
Re: [Dbix-class] Fixing my relationships to get working queries
The question as you wrote it is good, I just need to clarify some extra things before I answer it: On 01/19/2015 07:23 PM, John Stoffel wrote: ../bin/dbic-test2.pl carpenter SELECT me.name_id, me.full_name, me.last_name, me.first_name, me.comments, account.account_id, account.account_number, account.boxfolder_id, account.name_id, account.url, account.comments, account.volume, account.box, account.folder, account.range FROM (SELECT me.name_id, me.full_name, me.last_name, me.first_name, me.comments FROM names me WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC LIMIT ?) me LEFT JOIN account account ON account.name_id = me.name_id WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC: '[[::]]carpenter[[::]]', '10', '[[::]]carpenter[[::]]' Full Name: Carpenter, John (7333) account_id=3176 boxfolder_id=423 URL: fol01 V=35 F=fol01 R= account_id=3177 boxfolder_id=423 URL: fol01 V=35 F=fol01 R= account_id=3178 boxfolder_id=423 URL: fol01 V=35 F=fol01 R= Full Name: Carpenter, John Jr. (7334) account_id=3179 boxfolder_id=106 URL: b08f01 V=16 F=1 R=6003-6128 account_id=3180 boxfolder_id=112 URL: b08f07 V=16 F=7 R=6780-6806 account_id=3181 boxfolder_id=122 URL: b09f02 V=17 F=2 R=6999-7125 account_id=3182 boxfolder_id=122 URL: b09f02 V=17 F=2 R=6999-7125 You show your script as executing one query (there is only one line above), yet further down you say: And I'm completely confused why there are multiple SELECTs since the whole idea of prefetch was to just pull in the info ahead of time Um... yes - which one is it? Did yoiu get a single SELECT statement or multiple? Do you want me to send you more details on the Schema I have? Nope, what we have so far is plenty. ___ 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] Fixing my relationships to get working queries
Peter == Peter Rabbitson rabbit+d...@rabbit.us writes: Peter The question as you wrote it is good, I just need to clarify some extra Peter things before I answer it: Peter On 01/19/2015 07:23 PM, John Stoffel wrote: ../bin/dbic-test2.pl carpenter SELECT me.name_id, me.full_name, me.last_name, me.first_name, me.comments, account.account_id, account.account_number, account.boxfolder_id, account.name_id, account.url, account.comments, account.volume, account.box, account.folder, account.range FROM (SELECT me.name_id, me.full_name, me.last_name, me.first_name, me.comments FROM names me WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC LIMIT ?) me LEFT JOIN account account ON account.name_id = me.name_id WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC: '[[::]]carpenter[[::]]', '10', '[[::]]carpenter[[::]]' Full Name: Carpenter, John (7333) account_id=3176 boxfolder_id=423 URL: fol01 V=35 F=fol01 R= account_id=3177 boxfolder_id=423 URL: fol01 V=35 F=fol01 R= account_id=3178 boxfolder_id=423 URL: fol01 V=35 F=fol01 R= Full Name: Carpenter, John Jr. (7334) account_id=3179 boxfolder_id=106 URL: b08f01 V=16 F=1 R=6003-6128 account_id=3180 boxfolder_id=112 URL: b08f07 V=16 F=7 R=6780-6806 account_id=3181 boxfolder_id=122 URL: b09f02 V=17 F=2 R=6999-7125 account_id=3182 boxfolder_id=122 URL: b09f02 V=17 F=2 R=6999-7125 Peter You show your script as executing one query (there is only one line Peter above), yet further down you say: And I'm completely confused why there are multiple SELECTs since the whole idea of prefetch was to just pull in the info ahead of time I'm talking about the multiple SELECT statements all mashed together into the query above which stars with: SELECT me.name_id, me.ful_name Why does it need to use TWO regexp matches in the query? The one I did by hand up above seems (ha!!) to be more efficient and certainly clearer about what I'm trying to do. And it's much more of what I would expect from the info provided in: http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#JOINS_AND_PREFETCHING Peter Um... yes - which one is it? Did yoiu get a single SELECT statement or Peter multiple? So maybe I should have said a query with multiple SELECTs in it, when only one is needed. ___ 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] Error on sub class
Petter if I remove the check (line 1909) everything else seems to behave sensibly. Before you just go off and remove it I noticed that there is a little more going on here. We are using Catalyst and it is called for simplicity YYY. I just noticed that the error message is that it is expected a YYY::Model::DB::Document.pm, while the parent package name is YYY::Schema::Document.pm On Mon, Jan 19, 2015 at 5:17 AM, Peter Rabbitson rabbit+d...@rabbit.us wrote: On 01/16/2015 11:41 AM, Mitchell Elutovich wrote: $perl -v This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi $ perl -MDBIx::Class -le 'print $DBIx::Class::VERSION' 0.082810 Anyone know why I might be now running into this? I'm not sure how long this problem has existed and I thought I was originally using the sub class ok. This is a new check that was introduced during the 0.0828xx series. It was introduced due to a lot of abuse-cases of the relationship system. Nevertheless in this case it seems like a false positive - i.e. your usage seems valid. Can you please disable the exception-check in question in DBIx::Class::ResultSource::_resolve_relationship_condition, and tell me if everything else seems to behave in a sensible manner? If this is the case - I would have to remove this check going forward. ___ 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
