0

I wrote an override in Joomla 3.X and sorted the result from the DB using this query, which works:

$query = "SELECT content.*, fieldvalues.*
          FROM #__content AS content, #__fields_values AS fieldvalues
          WHERE content.catid=" . $catid . "
            AND content.id = fieldvalues.item_id
            AND fieldvalues.field_id=418
            AND fieldvalues.value LIKE '%" . $sorttype . "%'
          ORDER BY fieldvalues.value ASC;";

I would like to sort this result in multiple ways. Actual sortfield is the author, but if an author has published multiple titles I would like to sort it also by release date, which will be fieldvalues.field_id=412, but I have no idea how to create a functional SQL query.

Maybe you can help or give me a tip.

New contributor
Andreas Soraru is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
5
  • Welcome to JSE and thank you for your question. Please take our tour. Do we need to see some relevant rows from your #__fields_values table? I don't see a releasedate in the content table. You may need a pivot. If you would like me to help with this, I will need a dbfiddle demo link to play with. For example, see how Gart and I exchanged dbfiddle links here: joomla.stackexchange.com/q/28243/12352 – mickmackusa May 3 at 13:25
  • The problem for me is accessing more than one custom fiied for sorting ... so in the fields values table ther is only the id, the field-id and the value ... – Andreas Soraru May 3 at 13:38
  • I am very familiar with how the #__fields_values table is set up. I have a long track record of answering all of the questions on JSE that incorporate this table. The challenge is, can you provide the dbfiddle link containing enough sample data from both tables before I go to bed? When you can produce the fiddle, please add it to your question as an edit. Here is the meta link often referred to on StackOverflow when a schema is beneficial: meta.stackoverflow.com/a/333953/2943403 – mickmackusa May 3 at 13:46
  • @mickmackusa ... i have never used dbfiddle ... i will try to get that done – Andreas Soraru May 3 at 14:00
  • Don't give up on me. Login to your phpMyAdmin and Export your two tables to a txt file or as plain text, then copy the table creating and row inserting data into the left panel of dbfiddle. If the amount of data is too large or contains private data, you should trim down the total number of rows and obfuscate any private data before saving the fiddle demo. Once produced, share your hyperlink here. – mickmackusa May 4 at 23:30
0

Your best bet would be to use joomla db to get all the rows and then use php array sorts to analyse and sort the result. Something like

    $db = Factory::getDbo();

    // Create a new query object.
    $query = $db->getQuery(true);


    $query->select('content.*');
    $query->select('fieldsvalues.*');
    $query->from('`#__content` AS content');

    $query->from('`#__fields_values` AS fieldvalues');

    $query->where('content.id = fieldvalues.field_id');
    $query->where('content.catid = '.$db->quote($catid));
    $query->where('fieldvalues.field_id=418');
    $query->where('fieldvalues.value LIKE '%" . $db->quote($sorttype) . "%');
    $query->order('fieldvalues.value ASC');

    $db->setQuery($query);
    //output string to make sure is okay?
    //echo $query->__toString();exit();

    $rows = $db->loadObjectList();

    foreach ($rows as $item)
    {
        
    }
    /* or do analysis and then use php sorting functions */

    return $rows;
New contributor
mfleeson is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
4
  • Did you test this locally? Can you use two from() calls like that? I've never tried it. Also, please read about escaping values used in LIKE joomla.stackexchange.com/a/22712/12352 – mickmackusa May 3 at 13:40
  • So there is no way to put that in a query? There are about 100 different queries which are used. – Andreas Soraru May 3 at 13:41
  • @And I am certain that it can be done in the sql. Please edit your question to include your table data in a dbfiddle and your exact desired resultset for your sample data. – mickmackusa May 3 at 13:43
  • 1
    Yes, tested locally. Multiple from lines are the same as doing $query->from(array('tablea','tableb')); – mfleeson May 3 at 14:42

Your Answer

Andreas Soraru is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.