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@lists.scsys.co.uk
