1

I need to convert a MySql query with subqueries to “chained query” format for Joomla. First of all: I am aware that the query can be rewritten without subqueries, but as a matter of performance I cannot avoid using them. So, the query is this:

SELECT `nome_doc` FROM ( SELECT `id_esameweb`, `nome_doc` FROM `referti_doc` WHERE `id_esameweb` = 100 AND `id` = 1 ) `d` INNER JOIN ( SELECT `id_esameweb` FROM `referti_esami` WHERE `id_paziente` = 1000 ) `e` ON (`e`.`id_esameweb` = `d`.`id_esameweb`);

How can I chaining this type of MySql query?

Thanks in advance

Improve this question
1
  • 1
    If you paste the solution in an Answer and accept the answer that would be appreciated. Commented Jul 2 at 22:09

1 Answer 1

Reset to default
1

SOLVED! I hope it can be helpful to someone:

$dQuery = $db->getQuery(true) ->select($db->quoteName(['id_esameweb', 'nome_doc'])) ->from($db->quoteName('#__referti_doc')) ->where($db->quoteName('id_esameweb') . ' = :idesame') ->where($db->quoteName('id') . ' = :iddoc'); $eQuery = $db->getQuery(true) ->select($db->quoteName(['id_esameweb'])) ->from($db->quoteName('#__referti_esami')) ->where($db->quoteName('id_paziente') . ' = :idpaziente') ->where($db->quoteName('id_esameweb') . ' = :idesame'); $query = $db->getQuery(true) ->select($db->quoteName(['nome_doc'])) ->from('('.$dQuery.') '.$db->quoteName('d')) ->join('INNER', '('.$eQuery.') '.$db->quoteName('e'). ' ON (' . $db->quoteName('e.id_esameweb') . ' = ' . $db->quoteName('d.id_esameweb') . ')') ->bind(':idesame', $ids[0], ParameterType::INTEGER) ->bind(':iddoc', $ids[1], ParameterType::INTEGER) ->bind(':idpaziente', $idPaziente, ParameterType::INTEGER); $db->setQuery($query);
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.