[Dbix-class] inner join and date extraction

RAPPAZ Francois francois.rappaz at unifr.ch
Wed Jul 2 08:20:08 GMT 2014


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 



More information about the DBIx-Class mailing list