Need SQL queries ? How to solve this?

Need Help
1

I have three tables and it’s Join with each other . Table A, B, C
Table A and Table B Join and result of the table AB get only order by created desc only one record for Table B
Same above scenario
Table A and Table C Join and result of the table AB get only order by created desc only one record for Table C
Let me explain in technically :

ORDER BY B.created Desc Limit 1

ORDER BY C.created Desc Limit 1

I want both order by clauses used in same query result i tried added AND, OR but not works how this use for single query

I need like this as follows.

ORDER BY B.created Desc Limit 1 AND ORDER BY C.created Desc Limit 1

2

Can you show the code you’re using in your query?

3

Here is my code !

SELECT Students.*,
STATES.NAME AS STATE_NAME,
USERS.USERNAME,student_answers.answer as enrollno,
payments.created as paid,
overall_grade.grade as grade
FROM Students
LEFT JOIN USERS ON USERS.ID = (Students.USER_ID)
LEFT JOIN STATES ON STATES.ID = (Students.STATE_ID)
LEFT JOIN student_answers ON student_answers.student_id=students.id
LEFT JOIN payments ON payments.student_id=students.id
LEFT JOIN overall_grade ON overall_grade.student_id=students.id
LEFT JOIN payments ON payments.student_id=students.id
WHERE users.active=true and
student_answers.question_id = 43
and student_answers.answer BETWEEN ‘05/27/2021’ AND ‘12/31/2021’
ORDER BY overall_grade.created Desc Limit 1

SELECT Students.*,
STATES.NAME AS STATE_NAME,
USERS.USERNAME,student_answers.answer as enrollno,
payments.created as paid,
overall_grade.grade as grade
FROM Students
LEFT JOIN USERS ON USERS.ID = (Students.USER_ID)
LEFT JOIN STATES ON STATES.ID = (Students.STATE_ID)
LEFT JOIN student_answers ON student_answers.student_id=students.id
LEFT JOIN payments ON payments.student_id=students.id
LEFT JOIN overall_grade ON overall_grade.student_id=students.id
LEFT JOIN payments ON payments.student_id=students.id
WHERE users.active=true and
student_answers.question_id = 43
and student_answers.answer BETWEEN ‘05/27/2021’ AND ‘12/31/2021’
ORDER BY payments.created Desc Limit 1

This the single query but I want need apply both order by clause and limit for single query How can do this or Other way do

4

So, the queries are the same other than the order? You are trying to get the most recently created grade, and also the most recently created payment? (Side question, why are you joining with payments twice? Is this actually working?)

I think what you probably want is to use the queryBuilder option in contain to put the limit and order on the grade and payment records, something like

$student = $this->Students->find()
    ->contain([
        'States', 'Users',
        'StudentAnswers' => ['queryBuilder' => function (Query $q) {
            return $q->where(['StudentAnswers.question_id' => 43]); // Not 100% sure how to add the "BETWEEN condition to this
        }],
        'Payments' => ['queryBuilder' => function (Query $q) {
            return $q->order(['Payments.created' => 'DESC'])->limit(1);
        }],
        'OverallGrade' => ['queryBuilder' => function (Query $q) {
            return $q->order(['OverallGrade.created' => 'DESC'])->limit(1);
        }],
    ])
    ->where(['Users.active' => true]);
5

Thanks @Zuluru but i solved this query by add , separated order by variables

6

A single query with all those joins is likely to run more slowly, particularly when your datasets get large, than what I’ve suggested, but if it’s fast enough, then that’s the main thing.

7

@Zuluru

For reference: You can add between condition like this:

$student = $this->Students->find()
    ->contain([
        'StudentAnswers' => ['queryBuilder' => function (Query $q) {
            $between = $q->newExpr()->between('StudentAnswers.answer', '2021-05-27', '2021-12-31');

            return $q->where(['StudentAnswers.question_id' => 43, $between]);
        }],
    ]);
1 Like
8

@Mentis Thanks for reply, I try this on my code.

1 Like