Re: DBIx HAVING and COUNT error
sorry i didnt understand.
how can this have it?
my $obj_rs = $schema->resultset('itemList')->serach(
{ t_id => { -in => [ $tStr ] }},
{
group_by => [ qw(i_id) ],
HAVING COUNT('t_id') = $tCount
}
);
On Saturday, September 3, 2016 1:37 AM, Ron Savage
wrote:
Hi Rajeev
On 03/09/16 15:58, Rajeev Prasad wrote:
> I tried what you said and also what manual says, but it is not working
> my tries:
> HAVING => { count('t_id') => $tCount}
> HAVING => {'COUNT_t_id' => {'=', $tCount } }
> HAVING => { count('t_id') => {'=', $tCount } }
>
> having => { 'count_employee' => { '>=', 100 } }
Those docs tell me that the SQl has to include something like:
'select ... count(t_id) as count_t_id ...
And then the having can be
having => {count_t_id => {'=', $tCount} }
--
Ron Savage - savage.net.au
Re: DBIx HAVING and COUNT error
I tried what you said and also what manual says, but it is not working
my tries:HAVING => { count('t_id') => $tCount}
HAVING => {'COUNT_t_id' => {'=', $tCount } }HAVING => { count('t_id') => {'=',
$tCount } }
I get err.
the manual says:
having
- Value: $condition
The HAVING operator specifies a secondary condition applied to the set after
the grouping calculations have been done. In other words it is a constraint
just like "where" (and accepting the same SQL::Abstract syntax) applied to the
data as it exists after GROUP BY has taken place. Specifying "having" without
"group_by" is a logical mistake, and a fatal error on most RDBMS engines.E.g.
having => { 'count_employee' => { '>=', 100 } }or with an in-place function in
which case literal SQL is required: having => \[ 'count(employee) >= ?', 100 ]
On Saturday, September 3, 2016 12:51 AM, Ron Savage <r...@savage.net.au>
wrote:
Hi Rajeev
On 03/09/16 11:38, Rajeev Prasad via dbi-users wrote:
> DBIx error in webserver log:
>
> [cgi:error] [pid 29640] ... AH01215: Can't locate object method "HAVING"
> via package "COUNT" (perhaps you forgot to load "COUNT"?)
>
>
> the query:
>
> my $obj_rs = $schema->resultset('itemList')->serach(
> { t_id => { -in => [ $tStr ] }},
> {
> group_by => [ qw(i_id) ],
> HAVING COUNT('t_id') = $tCount
What is this? The syntax you have used is simple not Perl.
I did not check the manual, but try (wild guess):
"having count('t_id')" => $tCount,
> }
> );
>
>
> currently database has no records which match the query. but i was not
> expecting such an error.
>
> what is wrong in my statement?
>
> thank you.
> Rajeev
--
Ron Savage - savage.net.au
DBIx HAVING and COUNT error
DBIx error in webserver log:
[cgi:error] [pid 29640] ... AH01215: Can't locate object method "HAVING" via
package "COUNT" (perhaps you forgot to load "COUNT"?)
the query:
my $obj_rs = $schema->resultset('itemList')->serach(
{ t_id => { -in => [ $tStr ] }},
{
group_by => [ qw(i_id) ],
HAVING COUNT('t_id') = $tCount
}
);
currently database has no records which match the query. but i was not
expecting such an error.
what is wrong in my statement?
thank you.Rajeev
Re: how to repersent N:M in DBIx?
hi Martin, same set of IDs for all products. products may have multiple and more tag-IDs, but i will be looking for only the given set of IDs - for all the products. I could not figure to translate that SQL into DBIx syntax. thank you.Rajeev On Thursday, September 1, 2016 3:37 AM, Martin Hall <martin.h...@oracle.com> wrote: Don't think this counts as a DBI question really as the best solution would be to do it all in a single piece of SQL, rather than bringing the data back for local processing. You're not clear in your description whether you are looking for the same set of tag IDs for every product or different sets. If it's the former, then you have a solution. If it's the latter you need to be clear on how you will know that a product has the complete set of tags and that will likely require some form of additional lookup table. On 01/09/2016 04:06, Rajeev Prasad via dbi-users wrote: Pl refer to this link, it has my exact problem (but it only tells about SQL solution): http://stackoverflow.com/questions/11409869/mysql-nm-relationship-find-rows-with-several-specific-relations I have three tables: 1. products:-> prod-id, prod-name 2. tags:-> tag-id, tag-name 3. product_tags::-> prod-id, csv_tag_id i read elsewhere on internet that this is not a good database/table design. so i am ready to change that too, but i could not think of any other way to represent this relationship. a given product could have multiple tags, but i have to find - at a given time - only those products which have 'ALL' of multiple given tags. the SQL suggested on the page is: SELECT a.* FROM products a INNER JOIN product_tags b ON a.product_id = b.product_id WHERE b.tag_id IN (1,23,54) GROUP BY a.product_id HAVING COUNT(1) = 3 thank you. Rajeev -- cheers Martin | |
how to repersent N:M in DBIx?
Pl refer to this link, it has my exact problem (but it only tells about SQL solution): http://stackoverflow.com/questions/11409869/mysql-nm-relationship-find-rows-with-several-specific-relations I have three tables:1. products:-> prod-id, prod-name 2. tags:-> tag-id, tag-name 3. product_tags::-> prod-id, csv_tag_id i read elsewhere on internet that this is not a good database/table design. so i am ready to change that too, but i could not think of any other way to represent this relationship. a given product could have multiple tags, but i have to find - at a given time - only those products which have 'ALL' of multiple given tags. the SQL suggested on the page is: SELECT a.* FROM products a INNER JOIN product_tags b ON a.product_id = b.product_id WHERE b.tag_id IN (1,23,54) GROUP BY a.product_id HAVING COUNT(1) = 3thank you.Rajeev
