CODEWITHSUNDEEP
phpsqljoomla
Julian Moreno
Julian Moreno

Reputation: 1104

Adapting query to JDatabase

How can I adapt the following query to JDatabase?

SELECT
     c.RAZONSOCIAL                                      usu_razonSocial
    ,c.NIT                                              usu_nit
    ,c.SEDE                                             usu_sede
    ,c.EMAIL                                            usu_email
    ,IF(IFNULL(u.block, 1) = 0, 'Activo', 'Inactivo')   usu_estado
    ,COUNT(ct.ctf_id)                                   nroCertificados
FROM
    cargacliente c

    INNER JOIN 
    cargas 
    ON (crg_id = cargas_id) 
        AND (crg_status = 'Ok')

    INNER JOIN 
    certificados ct 
    ON (ctf_sede = SEDE)
        AND (ctf_nit = NIT)

    LEFT JOIN 
    database_1.bml_users u 
    ON (id = user_id)
GROUP BY
     c.NIT
    ,c.SEDE
ORDER BY
     usu_razonSocial
    ,usu_nit
    ,usu_sede;

This is Joomla 2.5.4.

I have read this post but I couldn't do it.

I tried to do it in this way:

<?php

$query
    ->select($db->quoteName(array('c.RAZONSOCIAL AS usu_razonSocial', 'c.NIT usu_nit', 'c.SEDE usu_sede', 'c.EMAIL usu_email', 'IF(IFNULL(u.block, 1) = 0, \'Activo\', \'Inactivo\') usu_estado', 'COUNT(ct.ctf_id) nroCertificados')))

    ->from($db->quoteName('database_2.cargacliente', 'c'))

    ->join('INNER', $db->quoteName('database_2.cargas','a') . ' ON (' . $db->quoteName('a.crg_id') . ' = ' . $db->quoteName('database_2.cargacert.cargas_id') . ') AND (' . $db->quoteName('a.crg_status') . ' = \'Ok\')')

    ->join('INNER', $db->quoteName('database_2.certificados','b') . ' ON (' . $db->quoteName('b.ctf_sede') . ' = ' . $db->quoteName('database_2.cargacliente.SEDE') . ') AND (' . $db->quoteName('b.ctf_nit') . ' = NIT)')

    ->join('LEFT', $db->quoteName('joomla_database.bml_users', 'u') . ' ON (' . $db->quoteName('database_2.usuario.usu_id') . ' = ' . $db->quoteName('joomla_database.bml_users.user_id') . ')')

    ->group(array('c.NIT', 'c.SEDE'))
    ->order(array('database_2.usuario.usu_razonSocial', 'database_2.usuario.usu_nit', 'database_2.usuario.usu_sede'));

?>

The error shown is the following:

500 - Ha ocurrido un error.
Unknown column 'c.RAZONSOCIAL usu_razonSocial' in 'field list' SQL=SELECT `c`.`RAZONSOCIAL usu_razonSocial`,`c`.`NIT usu_nit`,`c`.`SEDE usu_sede`,`c`.`EMAIL usu_email`,`IF(IFNULL(u`.`block, 1) = 0, 'Activo', 'Inactivo') usu_estado`,`COUNT(ct`.`ctf_id) nroCertificados` FROM `biochemical`.`cargacliente` AS `c` INNER JOIN `biochemical`.`cargas` AS `a` ON (`a`.`crg_id` = `biochemical`.`cargacert`.`cargas_id`) AND (`a`.`crg_status` = 'Ok') INNER JOIN `biochemical`.`certificados` AS `b` ON (`b`.`ctf_sede` = `biochemical`.`cargacliente`.`SEDE`) AND (`b`.`ctf_nit` = NIT) LEFT JOIN `biochemical_bml`.`bml_users` AS `u` ON (`biochemical`.`usuario`.`usu_id` = `biochemical_bml`.`bml_users`.`user_id`) GROUP BY c.NIT,c.SEDE ORDER BY biochemical.usuario.usu_razonSocial,biochemical.usuario.usu_nit,biochemical.usuario.usu_sede

Upvotes: 0

Views: 219

Answers (2)

Julian Moreno
Julian Moreno

Reputation: 1104

The changes were the following:

  1. Remove the first $db->quoteName() in the SELECT statement.
  2. Modify ALIAS from tables.
  3. LEFT JOIN modification.

The final code is the following:

$query
    ->select(array('c.RAZONSOCIAL AS usu_razonSocial', 'c.NIT AS usu_nit', 'c.SEDE AS usu_sede', 'c.EMAIL AS usu_email', 'IF(IFNULL(u.block, 1) = 0, \'Activo\', \'Inactivo\') AS usu_estado', 'COUNT(b.ctf_id) AS nroCertificados'))

    ->from($db->quoteName('database_2.cargacliente', 'c'))

    ->join('INNER', $db->quoteName('database_2.cargas','a') . ' ON (' . $db->quoteName('a.crg_id') . ' = ' . $db->quoteName('c.cargas_id') . ') AND (' . $db->quoteName('a.crg_status') . ' = \'Ok\')')

    ->join('INNER', $db->quoteName('database_2.certificados','b') . ' ON (' . $db->quoteName('b.ctf_sede') . ' = ' . $db->quoteName('c.SEDE') . ') AND (' . $db->quoteName('b.ctf_nit') . ' = c.NIT)')

    ->join('LEFT', $db->quoteName('joomla_database.bml_users', 'u') . ' ON (' . $db->quoteName('c.user_id') . ' = ' . $db->quoteName('u.id') . ')')

    ->group(array('c.NIT', 'c.SEDE'))
    ->order(array('usu_razonSocial', 'usu_nit', 'usu_sede'));

Upvotes: 1

Radek Suski
Radek Suski

Reputation: 1392

This is a bit difficult to say what the problem is without having the right environment. I would suggest to debug through the driver to see where it fails. You can of course also put the query direct through the "setQuery" method. It would be however compatible with MySQL only.

But, once again, please update your Joomla! to the latest version ASAP.

Upvotes: 0

Related Questions