Re: [Dbix-class] Complex joins
Brilliant! That is indeed the answer I was (blindly) looking for. Thanks greatly. Iain. On 10 April 2013 20:46, Matthew Phillips wrote: > Hi Lain, > What you are looking for is the coderef syntax for declaring a > relationship as describe here: > https://metacpan.org/module/DBIx::Class::Relationship::Base#condition > > Cheers, > Matt > > > On Wed, Apr 10, 2013 at 3:40 PM, Iain C Docherty < > dbix-cl...@iain-docherty.com> wrote: > >> I have googled for this, as far as I can tell DBIx::Class does not >> support complex joins. An example of which is below. >> >> select star.id,star.name,body.id,body.name,building.class,empire.name >> from star >> LEFT JOIN probes >> ON star.id = probes.star_id >> AND probes.alliance_id=26 >> LEFT JOIN body >> ON star.id = body.star_id >> AND probes.id is not null >> LEFT JOIN empire >> ON body.empire_id = empire.id >> LEFT JOIN building >> ON body.id = building.body_id >> AND building.class='Lacuna::DB::Result::Building::Permanent::Ravine' >> WHERE star.x > -10 >> AND star.x < 10 >> AND star.y > -10 >> AND star.y < 10 >> group by body.id >> >> Is there any way that this could be implemented in DBIx::Class or do I >> have to drop down to SQL and implement a custom ResultSource? >> >> (and in case you are wondering, this is to do with the open source >> project at https://github.com/plainblack/Lacuna-Server-Open ) >> >> -- >> Kind Regards >> Iain >> >> ___ >> 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 > ___ 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] Complex joins
Hi Lain, What you are looking for is the coderef syntax for declaring a relationship as describe here: https://metacpan.org/module/DBIx::Class::Relationship::Base#condition Cheers, Matt On Wed, Apr 10, 2013 at 3:40 PM, Iain C Docherty < dbix-cl...@iain-docherty.com> wrote: > I have googled for this, as far as I can tell DBIx::Class does not support > complex joins. An example of which is below. > > select star.id,star.name,body.id,body.name,building.class,empire.name > from star > LEFT JOIN probes > ON star.id = probes.star_id > AND probes.alliance_id=26 > LEFT JOIN body > ON star.id = body.star_id > AND probes.id is not null > LEFT JOIN empire > ON body.empire_id = empire.id > LEFT JOIN building > ON body.id = building.body_id > AND building.class='Lacuna::DB::Result::Building::Permanent::Ravine' > WHERE star.x > -10 > AND star.x < 10 > AND star.y > -10 > AND star.y < 10 > group by body.id > > Is there any way that this could be implemented in DBIx::Class or do I > have to drop down to SQL and implement a custom ResultSource? > > (and in case you are wondering, this is to do with the open source project > at https://github.com/plainblack/Lacuna-Server-Open ) > > -- > Kind Regards > Iain > > ___ > 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] Complex joins
I have googled for this, as far as I can tell DBIx::Class does not support complex joins. An example of which is below. select star.id,star.name,body.id,body.name,building.class,empire.name from star LEFT JOIN probes ON star.id = probes.star_id AND probes.alliance_id=26 LEFT JOIN body ON star.id = body.star_id AND probes.id is not null LEFT JOIN empire ON body.empire_id = empire.id LEFT JOIN building ON body.id = building.body_id AND building.class='Lacuna::DB::Result::Building::Permanent::Ravine' WHERE star.x > -10 AND star.x < 10 AND star.y > -10 AND star.y < 10 group by body.id Is there any way that this could be implemented in DBIx::Class or do I have to drop down to SQL and implement a custom ResultSource? (and in case you are wondering, this is to do with the open source project at https://github.com/plainblack/Lacuna-Server-Open ) -- Kind Regards Iain ___ 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] select specific columns on resultset
Ok, sorry, I copied the wrong example, I tried with: my @recs=$self->db->resultset($self->table)->search({},{columns => [qw/ id /],},)->all; but I can see the Name column for example in the foreach loop. After some debugging on mysql I found some stranges: 67 Query SELECT me.id FROM ReportTemplates me 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '124' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '125' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '126' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '127' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '135' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '136' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '146' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '150' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '177' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '184' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '192' ) 67 Query SELECT me.Name FROM ReportTemplates me WHERE ( me.id = '193' ) 67 Query SELECT me.id, me.OperationId, me.Name, me.Description, me.Content, me.lastLogin, me.prevLogin FROM ReportTemplates me WHERE ( me.id= '124' ) 67 Query SELECT me.id, me.OperationId, me.Name, me.Description, me.Content, me.lastLogin, me.prevLogin FROM ReportTemplates me WHERE ( me.id= '125' ) 67 Query SELECT me.id, me.OperationId, me.Name, me.Description, me.Content, me.lastLogin, me.prevLogin FROM ReportTemplates me WHERE ( me.id= '126' ) 67 Query SELECT me.id, me.OperationId, me.Name, me.Description, me.Content, me.lastLogin, me.prevLogin FROM ReportTemplates me WHERE ( me.id= '127' ) 67 Query SELECT me.id, me.OperationId, me.Name, me.Description, me.Content, me.lastLogin, me.prevLogin FROM ReportTemplates me WHERE ( me.id= '135' ) 67 Query SELECT me.id, me.OperationId, me.Name, me.Description, me.Content, me.lastLogin, me.prevLogin FROM ReportTemplates me WHERE ( me.id= '136' ) 67 Query SELECT me.id, me.OperationId, me.Name, me.Description, me.Content, me.lastLogin, me.prevLogin FROM ReportTemplates me WHERE ( me.id= '146' ) 67 Query SELECT me.id, me.OperationId, me.Name, me.Description, me.Content, me.lastLogin, me.prevLogin FROM ReportTemplates me WHERE ( me.id= '150' ) 67 Query SELECT me.id, me.OperationId, me.Name, me.Description, me.Content, me.lastLogin, me.prevLogin FROM ReportTemplates me WHERE ( me.id= '177' ) 67 Query SELECT me.id, me.OperationId, me.Name, me.Description, me.Content, me.lastLogin, me.prevLogin FROM ReportTemplates me WHERE ( me.id= '184' ) the first query is correct but the others ? I suspect that is not related with dbix, but why I can see column if I have not specified them ? thanks 2013/4/10 Peter Rabbitson > On Tue, Apr 09, 2013 at 03:58:53PM +0200, max wrote: > > Thanks for the answer, this piece of code is inside a perl sub, I tried > to > > extract the array elements as: > > > > foreach my $row (@recs) > > { > > my $id=$row->id; > > my $name=$row->Name; > > print MYFILE "recs name: $name, id: $id\n\n"; > > } > > > > and I can see the Name element for example. > > Well... you asked for it didn't you? (in fact you asked twice) > > > columns => [qw/id Name OperationId Name /] > > I am at this point confused what is your problem exactly... > > > I tried to insert the DBIC_TRACE > > in the sub ouputting it to a file but it's always empty ( > > $self->db->storage->debugfh(IO::File->new('file','w'));), is there > another > > way to use it ? > > When you use it this way you also need to ->storage->debug(1) for things > to start working. During debugging DBIC_TRACE=1 with dump to screen is > much more common, hence why noone answered quuckly ;) > > 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 > ___ 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