inner join and date extraction

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

inner join and date extraction

RAPPAZ Francois
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@...
Reply | Threaded
Open this post in threaded view
|

Re: 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:[hidden email]]
> 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-
> [hidden email]

_______________________________________________
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@...