CODEWITHSUNDEEP
mysqlsqlsql-serveryiiyii2
Saltern
Saltern

Reputation: 1383

add beetween option in $query in yii2

I want use where in $query

    $query->where(['and', "SUM(opportunity_conditions.money)>=10", "SUM(opportunity_conditions.money)<=100"]);

This is my $query : 

   $query = (new \yii\db\Query())->select(['SUM(opportunity_conditions.money),(opportunity_conditions.money)', 'opportunity.id', 'opportunity.logo', 'profile_details.user_id', 'opportunity.name', 'profile_details.co_name', 'opportunity.address', 'opportunity.project_type_id', 'opportunity.state_id'])->from('opportunity ')->innerJoin('profile_details', 'opportunity.user_id=profile_details.user_id')->where('opportunity_conditions.who=2')->innerJoin('opportunity_conditions', 'opportunity.id=opportunity_conditions.opportunity_id')->addGroupBy('opportunity.id')->orderBy('opportunity.id desc');

I need my query has result where:

 SUM(opportunity_conditions.money)>=10 and SUM(opportunity_conditions.money)<=100

This is my error :

SQLSTATE[HY000]: General error: 1111 Invalid use of group function

The SQL being executed was: 

SELECT  SUM(opportunity_conditions.money),
        (opportunity_conditions.money),
        opportunity.id,
        opportunity.logo,
        profile_details.user_id,
        opportunity.name,
        profile_details.co_name,
        opportunity.address,
        opportunity.project_type_id,
        opportunity.state_id
FROM opportunity 
INNER JOIN profile_details 
    ON opportunity.user_id=profile_details.user_id 
INNER JOIN opportunity_conditions 
    ON opportunity.id=opportunity_conditions.opportunity_id 
WHERE 
    ((SUM(opportunity_conditions.id)>=1) AND 
    (SUM(opportunity_conditions.id)<=1)) AND 
    (opportunity.id IN (27, 28)) 
GROUP BY opportunity.id 
ORDER BY opportunity.id DESC

Upvotes: 0

Views: 90

Answers (2)

Parth
Parth

Reputation: 215

Ok I think you can figure out this way.

first check the query is correct? we can get query from following command

var_dump($query->createCommand()->rawSql);

Now check this query or run this in database, remake the query if you a find mistake.

Upvotes: 0

Igbanam
Igbanam

Reputation: 6082

Try replacing where() with having(). Then add opportunity_conditions.who to the select() list.

Upvotes: 2

Related Questions