Answer the question
In order to leave comments, you need to log in
Are there standards for writing complex nested SQL queries?
To recalculate statistics, you have to write complex queries with a lot of nesting, like:
UPDATE staff
SET rating = (
(
SELECT SUM(value * weight) AS a
FROM `ratings`
WHERE entity_type = 'DOCTOR'
AND entity_id = staff.url
AND entity_city = (SELECT city
FROM clinics
WHERE clinics.account_id = staff.account_id
)
AND published = 1
)
/
(
SELECT SUM(weight) AS b
FROM `ratings`
WHERE entity_type = 'DOCTOR'
AND entity_id = staff.url
AND entity_city = (SELECT city
FROM clinics
WHERE clinics.account_id = staff.account_id
)
AND published = 1
)
),
ratingCommentCount = (
SELECT COUNT(*) AS count
FROM `ratings`
WHERE entity_type = 'DOCTOR'
AND entity_id = staff.url
AND entity_city = (SELECT city
FROM clinics WHEREclinics.account_id = staff.account_id
)
AND published = 1
);
$aSumSql = 'SELECT SUM(value * weight)';
$bSumSql = 'SELECT SUM(weight)';
DB::update("UPDATE staff SET rating = ($aSumSql) / ($bSumSql), ...............");
Answer the question
In order to leave comments, you need to log in
there are no standards.
There is a rule - multi-line code must be uniform and formatted.
In such cases, I use strings to store the query and raw query to execute it.
Display all amounts in one query, and do an UPDATE with the second.
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question