Asked  7 Months ago    Answers:  5   Viewed   34 times

I want use WhereIn and Groupby in Same Query to fetch Result.

I've tried this:

$loadids=explode("#@*",$reciptdet->loading_id);
$loadingdatas=DB::table('loading')->groupBy('vehicle_no')->whereIn('id',$loadids)->get();

But I got this error message:

SQLSTATE[42000]: Syntax error or access violation: 1055 'sbrtpt.loading.id' isn't in GROUP BY (SQL: select * from loading where id in (14, 15, 16) group by vehicle_no)

 Answers

64

Short answer

In configdatabase.php --> "mysql" array

Set 'strict' => false to disable all.

.... or

You can leave 'strict' => true and add modes to "mysql" option in

'mysql' => [
       ...
       ....
       'strict' => true,
       'modes' => [
            //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION'
        ],
 ]

Detailed answer

You may not need to disable all strict options ... Kindly have a look on this answer about this issue.

Wednesday, March 31, 2021
 
Saurabh
answered 7 Months ago
33

Recommended approach if you put here only methods (not classes):

  1. Create file anywhere you want
  2. In composer.json make sure you add this file to files key inside autoload like this:

    "autoload": {
        // here other autoload things
    
        "files": ["app/Helpers/AnythingHelper.php"]
    },
    
  3. Run composerdump-autoload`

For classes obviously you should use standard PSR-4 autoloading

Wednesday, March 31, 2021
 
zhartaunik
answered 7 Months ago
85

After re-reading the docs, it is clear that this is intended behaviour. The docs say

As an alternative to mocking, you may use the Event facade's fake method to prevent all event listeners from executing.

As described in a comment, I have to create two tests. One for making sure that the event has been fired and contains the expected data and a second one for making sure that the event listeners get fired.

So, first Test:

Event::fake();

$order = factory(Order::class)->make();

event(new PaymentWasCompleted($order));

Event::assertDispatched(PaymentWasCompleted::class, function ($e) use ($order) {
    return $e->order->id === $order->id;
});

Second test:

Queue::fake();

$order = factory(Order::class)->make();

event(new PaymentWasCompleted($order));

Queue::assertPushed(GenerateInvoiceJob::class, function ($job) use ($order) {
    return $job->order->id === $order->id;
});

As expected, this tests pass.

Saturday, May 29, 2021
 
Amber
answered 5 Months ago
69

In your select you have an aggregate function sum and a set of column name, the error tell you that you have not specified the correct list of column name in group by clause . could be you should add more columns name in group by probably related to the profile_details, opportunity_conditions table

You have also ,(opportunity.id),(opportunity_conditions.money), (opportunity.mantaghe), why the () if you need sum you must add sum to all column

sum(opportunity.id), sum(opportunity_conditions.money),

sum(opportunity.mantaghe),

otherwise if thes are normal columns you should use the normal sintax without ()

opportunity.id, opportunity_conditions.money,opportunity.mantaghe,

I have tried to rewrite a possible query

 SELECT SUM(opportunity_conditions.money),
        `opportunity`.`id`,
        `opportunity_conditions.money`,
        `opportunity.mantaghe`, 
        `opportunity`.`time`, 
        `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` 7
INNER JOIN `opportunity_conditions` ON `opportunity`.`id`=`opportunity_conditions`.`opportunity_id` 
GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  
ORDER BY `opportunity`.`id` DESC

with group by on the essential column name (i hope)

GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  
Wednesday, July 28, 2021
 
Giovanni
answered 3 Months ago
16

Check in the config/database.php file in the mysql conection that the strict is false:

'strict' => false,

If is true, put in false.

Saturday, July 31, 2021
 
Szenis
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share