CODEWITHSUNDEEP
phpmysqlcodeignitercodeigniter-3
user3653474
user3653474

Reputation: 3854

Cannot get rows filtered by group rows in sql

i have a table where there is a start date and end date these rows are group by unique id for example row id 4,5 have group_identify_id 5700045 and row id 1,2,3 have group_identify_id 5700044.I have to select only those group where all the rows of group have start column greater than current date. For eg. if current date is 15 then 5700045 should not be shown in result.

id                start                       end        group_identify_id

1          2019-07-18 07:15:00  2019-07-18 08:15:00          5700044
2          2019-07-16 07:15:00  2019-07-16 08:15:00          5700044
3          2019-07-17 07:15:00  2019-07-17 08:15:00          5700044
4          2019-07-15 07:15:00  2019-07-15 08:15:00          5700045
5          2019-07-14 07:15:00  2019-07-14 08:15:00          5700045

result should be something like this:

result:

1          2019-07-18 07:15:00  2019-07-18 08:15:00          5700044
2          2019-07-16 07:15:00  2019-07-16 08:15:00          5700044
3          2019-07-17 07:15:00  2019-07-17 08:15:00          5700044

I was trying something like this:

$results =  $this->db->where('where('DATE(start) >=',$currentDate)->get('schedule_diary')->result();

but it matched the whole rows not group of rows.

Upvotes: 2

Views: 114

Answers (6)

Farooq Ahmed Khan
Farooq Ahmed Khan

Reputation: 4094

You can try this, raw query

select * from schedule_diary group by group_identify_id having start > CURDATE()

for codeigniter you can use

$this->db->table('schedule_diary')->group_by('group_identify_id')->having('start', $currentDate);

Upvotes: 0

Ashok Gadri
Ashok Gadri

Reputation: 518

Use this raw query or convert it into the codeignieter format

SELECT * FROM schedule_diary WHERE DATE(START) > "$currentDate" AND group_identify_id NOT IN (SELECT distinct group_identify_id FROM test_1 WHERE DATE(START) <= "$currentDate")

It will work perfectly.

Working screenshot..

SELECT * FROM schedule_diary WHERE DATE(START) > date("2019-07-14") AND group_identify_id NOT IN (SELECT distinct group_identify_id FROM schedule_diary WHERE DATE(START) <= date("2019-07-14"))

enter image description here

Upvotes: 0

jlosada
jlosada

Reputation: 46

UPDATE You could test with something like that if you have values null in group_identify_id:

  /*Firstly, you should to select a set of rows whiches not in date*/
    $this->db->where('t1.group_identify_id = schedule_diary.group_identify_id');
    $this->db->where('DATE(t1.start) <= GETDATE()');
    $subquery = $this->db->get_compiled_select('schedule_diary as t1', TRUE);

  /*For last, you select all rows not exists in the subquery set.*/
    $this->db->where("not exists ($subquery)");
    $query = $this->db->get('schedule_diary');

    if($query){
      return $query->result_array();
    }

I think is right code for you!

Upvotes: 1

PHP Geek
PHP Geek

Reputation: 4033

try this query:

$this->db->where('start >', $currentDate)->get('schedule_diary')->result();

Upvotes: 0

Guga Nemsitsveridze
Guga Nemsitsveridze

Reputation: 751

I'm not sure, but I think, this where is odd there:

$results =  $this->db->where('where('DATE(start) >=',$currentDate)->get('schedule_diary')->result();

Try to remove it in your query:

$results =  $this->db->where('DATE(start) >=',$currentDate)->get('schedule_diary')->result();

Upvotes: 0

ascsoftw
ascsoftw

Reputation: 3476

Below is the raw query that would give you the required Result.

SELECT * FROM schedule_diary WHERE group_identify_id NON IN ( SELECT group_identify_id FROM schedule_diary WHERE start  <= $currentDate) );

You need to convert this query into CodeIgniter Framework or you can also use $this->db->query('RawQueryHere')

Upvotes: 1

Related Questions