[Dbix-class] inner join and date extraction

[RAPPAZ Francois]

Well this does not give an error

	$rs = $self->{schema}->resultset('Commande')->search_rs(undef, {
			columns => [{'display' => {distinct => $rs->dt_SQL_pluck({ -ident => '.T_create'}, 'year_month') }}],
			
			prefetch =>  ['ComCred', 'ComUser' ],
		});

But it does not exclude duplicate value in the result from the year_month exctraction

Thanks for any suggestion

François

> -----Original Message-----
> From: RAPPAZ Francois [mailto:francois.rappaz at unifr.ch]
> Sent: mercredi, 2. juillet 2014 10:20
> To: DBIx::Class user and developer list
> Subject: [Dbix-class] inner join and date extraction
> 
> I have three tables that hold orders (Table Commandes) Accounts
> (Credits) and people that order stuff (Users).
> I want to extract the year and month from a Timestamp field (T_create)
> for the row of the Commandes table that have corresponding rows in
> Credits and Users.
> 
> The following sql (mysql) would do:
> 
> SELECT DISTINCT EXTRACT(YEAR_MONTH FROM T_create) as display FROM
> commandes INNER JOIN credits USING (ID_credit) INNER JOIN users USING
> (ID_user) ORDER BY display ASC
> 
> I have the code that says
> 
> $rs = $self->{schema}->resultset('Commande')->search_rs(undef, {
> 			columns => {display => $rs->dt_SQL_pluck({ -ident =>
> '.T_create'}, 'year_month')},
> 			prefetch => ['ComCred', 'ComUser'],
> 			distinct => 1,
> 		});
> 
> And in the Commande.pm module:
> __PACKAGE__->belongs_to(ComUser => 'Dbc::Schema::Result::User',
> {'foreign.id_user' => 'self.id_user'}); __PACKAGE__->belongs_to(ComCred
> => 'Dbc::Schema::Result::Credit',  {'foreign.id_credit' =>
> 'self.id_credit'});
> 
> That fails with
> DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception:
> DBD::mysql::st execute failed: Unknown column 'me.T_create' in 'field
> list'
> [for Statement "SELECT EXTRACT(YEAR_MONTH FROM me.T_create),
> ComCred.id_credit, ComCred.nom_credit, ComUser.id_user,
> ComUser.id_departement, ComUser.nom, ComUser.prenom, ComUser.username,
> ComUser.email, ComUser.password FROM (SELECT EXTRACT(YEAR_MONTH FROM
> me.T_create), me.id_commande, me.id_credit, me.id_user FROM Commandes
> me  JOIN Credits ComCred ON ComCred.id_credit = me.id_credit  JOIN
> Users ComUser ON ComUser.id_user = me.id_user WHERE ( me.id_commande =
> ? ) GROUP BY EXTRACT(YEAR_MONTH FROM me.T
> _create)) me  JOIN Credits ComCred ON ComCred.id_credit = me.id_credit
> JOIN Users ComUser ON ComUser.id_user = me.id_user WHERE (
> me.id_commande = ? )"
> with ParamValues: 0='0', 1='0']
> 
> Without the join condition I see that the date extraction work.
> What am I missing ?
> 
> Thanks !
> 
> François
> 
> _______________________________________________
> 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 at lists.scsys.co.uk