ERROR in GROUP_BY with query getting from two databases CODEIGNITER

Question

I have a query which joins tables from two different databases. It is already showing results but then I wanted to show only unique results because some results are redundant. So I added a GROUP BY to get only the unique results but an error appears.

This is my code:

public function search_results_accommodations($location,$from_date,$to_date,$bedroom,$guests)
{
    $this->db->select('*, akzapier.bookings.id as BOOKING_ID, akzapier.properties.id as PROPERTY_ID, ci_alexandrohomes.assigned_property.ID as ASSIGNED_PROPERTY_ID, ci_alexandrohomes.listings.ID as LISTING_ID');
    $this->db->from('akzapier.bookings');
    $this->db->join('akzapier.properties', 'akzapier.properties.id=akzapier.bookings.property_id', 'inner');
    $this->db->join('ci_alexandrohomes.assigned_property', 'ci_alexandrohomes.assigned_property.property_id=akzapier.properties.id', 'inner');
    $this->db->join('ci_alexandrohomes.listings', 'ci_alexandrohomes.listings.ID=ci_alexandrohomes.assigned_property.listing_id');
    $this->db->where('akzapier.bookings.check_in !=', $from_date);
    $this->db->where('akzapier.bookings.check_out !=', $to_date);
    $this->db->where('ci_alexandrohomes.listings.city', $location);
    $this->db->where('ci_alexandrohomes.listings.bedrooms', $bedroom);
    $this->db->where('ci_alexandrohomes.listings.guests', $guests);
    $this->db->group_by('akzapier.properties.id', 'ASC')
    $query = $this->db->get();
    return $query->result();
}

The error doesn't show up in the page so I converted it to SQL to see the real deal:

SELECT * akzapier.bookings.id as BOOKING_ID, akzapier.properties.id as PROPERTY_ID, ci_alexandrohomes.assigned_property.ID as ASSIGNED_PROPERTY_ID, ci_alexandrohomes.listings.ID as LISTING_ID
FROM akzapier.bookings 
INNER JOIN akzapier.properties ON akzapier.properties.id=akzapier.bookings.property_id
INNER JOIN ci_alexandrohomes.assigned_property ON ci_alexandrohomes.assigned_property.property_id=akzapier.properties.id
INNER JOIN ci_alexandrohomes.listings ON ci_alexandrohomes.listings.ID=ci_alexandrohomes.assigned_property.listing_id
WHERE akzapier.bookings.check_in != '2019-09-21'
AND akzapier.bookings.check_out != '2019-09-30'
AND ci_alexandrohomes.listings.city = ‘1’
AND ci_alexandrohomes.listings.bedrooms = '2'
AND ci_alexandrohomes.listings.guests = '4'
GROUP BY akzapier.bookings.property_id ASC

ERROR SAYS:

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'akzapier.bookings.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql have you seen this ? — Vivek Singh, Oct 21 '19 at 06:41

TrayMan · Answer 1 · 2019-10-21T06:51:15.503

0

In general, when you use GROUP BY, your SELECT statement must contain either aggregates (such as MAX(...), COUNT(...), etc.) or the columns must appear in GROUP BY. You have selected all of the fields, non-aggregated, with the asterisk *. In this case, it's complaining about the field akzapier.bookings.id, which is neither aggregated, nor in your GROUP BY.

If you actually want unique values, try SELECT DISTINCT, which will drop duplicate rows from the result.

edited Oct 21 '19 at 06:51

answered Oct 21 '19 at 06:48

TrayMan

7,180
3
24
33

can you show me how it's done please? i'm a bit overwhelmed – Azis Oct 21 '19 at 06:50
Just replace SELECT with SELECT DISTINCT and drop the GROUP BY. If that doesn't do what you want, please update your question to be more specific (i.e. what's wrong with the output and how would you like it to be different.) – TrayMan Oct 21 '19 at 06:56
I cant just use distinct because I need a lot of other columns – Azis Oct 21 '19 at 06:58

ERROR in GROUP_BY with query getting from two databases CODEIGNITER

1 Answers1