Facing difficulties to write a sql query in laravel

Question

We have three tables users,answers and stretches. When someone login to our system we stored user_id,created_at and updated_at in stretches table and when he/she logout updated deleted at in the same row.

Session time 30 minutes.

users table

id
f_name
l_name
email

answers table

id
user_id
body
created_at
updated_at

stretches table

id
user_id
created_at
updated_at
deleted_at

Now i want to get only those rows from the stretches table where an answer has been given. I wrote this query but it takes to much time but did not return accurate data.

  $query = "select count(a.id), a.user_id
      from answers a
      where a.created_at in 
            (select s.created_at 
                from stretches s 
                   where s.created_at >= a.created_at
            ) group by a.user_id";
  $results = DB::select(DB::raw($query));

I'd be curious to hear how some of you write this query.

Answers table

id | body | user_id |    created_at    |    updated_at 
10 | text |   10    | 2015-10-10 0:0:0 | 2015-10-10 0:0:0

Stretches Table

id | user_id |    created_at    |    updated_at    |    deleted_at 
 1 |   10    | 2015-10-00 0:0:0 | 2015-10-10 0:0:0 | 2015-10-20 0:0:0

Now i want to get those rows from stretches table where an answer has been given.

Query results should be:

id | user_id |    created_at    |   updated_at    |    deleted_at 
 1 |   10    | 2015-10-10 0:0:0 |2015-10-10 0:0:0 | 2015-10-10 0:0:0

Can you add some example of data in answers and stretches table and you expected output based on that? — Utsav
– Utsav, Commented Jan 2, 2016 at 8:04
Answers Table id body user_id created_at updated_at 10 text 10 2015-10-10 0:0:0 2015-10-10 0:0:0 Stretches Table id user_id created_at updated_at deleted_at 1 10 2015-10-00 0:0:0 2015-10-10 0:0:0 2015-10-20 0:0:0 Now i want to get those rows from stretches table where an answer has been given. Results id user_id created_at updated_at deleted_at 1 10 2015-10-10 0:0:0 2015-10-10 0:0:0 2015-10-10 0:0:0 — MD Minhajul ISLAM
– MD Minhajul ISLAM, Commented Jan 2, 2016 at 8:24

fabulaspb · Accepted Answer · 2016-01-02 09:18:09Z

0

If I correctly understood you. SQL query below will return only those records from table Stretches where answer was given to user. Use operator JOIN, it will do filter work for you.

SELECT S.user_id, COUNT(*) as answers_count FROM Stretches AS S
    JOIN Answers AS A ON S.user_id = A.user_id
GROUP BY S.user_id

edited Jan 2, 2016 at 9:18

answered Jan 2, 2016 at 9:05

fabulaspb

1,2538 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MxGutierrez · Accepted Answer · 2021-05-29 00:13:48Z

0

This should do the job

SELECT DISTINCT Stretches.* FROM Stretches
    JOIN Answers ON Stretches.user_id = Answers.user_id
GROUP BY Stretches.user_id

answered May 29, 2021 at 0:13

MxGutierrez

312 silver badges2 bronze badges

Collectives™ on Stack Overflow

Facing difficulties to write a sql query in laravel

users table

answers table

stretches table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

users table

answers table

stretches table

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related