1

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.

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
1

I have reverse engineered a minimal verifiable example on db-fiddle.com based on your question and answer details.

Using my reduced and generic sample data with the sql in your answer, the output is: (DB Fiddle Demo)

value field_id value field_id id title alias introtext fulltext catid field_id item_id value
documentary 417 2021-05-05 46 5 test 5 test-5 intro text 5 The full text 5 2 418 5 Andreas Soraru
science-fiction 417 2021-01-01 46 1 test 1 test-1 intro text 1 The full text 1 2 418 1 Andreas Soraru
science-fiction 417 2021-03-03 46 3 test 3 test-3 intro text 3 The full text 3 2 418 3 Andreas Soraru

This is selecting all articles where catid = 2 and the author's name (field_id = 418) contains Soraru, then it sorts by genre (field_id = 417) ASC, then release date (field_id = 46). You state that this works for you, but let me show you a cleaner, more modern, and less bloated way of gathering and sorting this data...

  1. I do not recommend using old-skool comma-JOINs as you are using to relate the content table to the fields_values table.
  2. Use a pivot technique instead of making multiple JOINs. This table is often gigantic and the fewer times you need to join the whole table in your sql, the better.
  3. Don't put data in your SELECT clause unless you need it. Those duplicated field_id columns are going to overwrite each other and you don't actually need them -- let's clear them away. I am going to assume that you need those duplicated value columns, so let's give them unique aliases so that you can access them.
  4. It is appropriate to use an INNER JOIN in this case because the HAVING clause necessitates the existance of related data in the fields_values table.

New / Recommended SQL: (DB Fiddle Demo)

SELECT lmnop_content.*,
       MAX(IF(field_id = 418, value, NULL)) AS author,
       MAX(IF(field_id = 417, value, NULL)) AS genre,
       MAX(IF(field_id = 46, value, NULL)) AS release_date
FROM lmnop_content
INNER JOIN lmnop_fields_values ON id = item_id
WHERE catid = 2
GROUP BY id
HAVING MAX(IF(field_id = 418, value, NULL)) LIKE '%Soraru%'
ORDER BY genre, release_date
id title alias introtext fulltext catid author genre release_date
5 test 5 test-5 intro text 5 The full text 5 2 Andreas Soraru documentary 2021-05-05
1 test 1 test-1 intro text 1 The full text 1 2 Andreas Soraru science-fiction 2021-01-01
3 test 3 test-3 intro text 3 The full text 3 2 Andreas Soraru science-fiction 2021-03-03

This new script is much cleaner, readable, and maintainable.

The Joomla query builder syntax should look something like this: (I didn't take the time to test this)

$db = Factory::getDbo();
$query = $db->getQuery(true);
$query->select(
    [
        "#__content.*",
        "MAX(IF(field_id = 418, value, NULL)) AS author",
        "MAX(IF(field_id = 417, value, NULL)) AS genre",
        "MAX(IF(field_id = 46, value, NULL)) AS release_date"
    ]
)
->from("#__content")
->innerJoin("#__fields_values ON id = item_id")
->where("cat_id = " . (int)$catid)
->group("id")
->having("MAX(IF(field_id = 418, value, NULL)) LIKE " . $db->q("%" . $db->escape($sorttype , true) . "%", false))
->order("genre, release_date");
$db->setQuery($query);
// var_export($db->loadAssocList());
3
  • Hi and many thanks ... i tried this and it is really faster and more effective. I had no idea to get that done like this cause of my very poor sql knowledge. This really works like a charm. Thanks a lot and i promise to get more into SQL and enlarge my knowledge. – Andreas Soraru May 15 at 10:03
  • If you want to learn more about pivot queries (these are vital in almost all queries executed on the #__fields_values table), please click the blue "pivot" link in my comment under your question. In other answers, I go into greater detail about how to work with "aggregate data". I am delighted to share my knowledge and to assist you in your professional growth. Look at how valuable it was for you to ask on JSE. Please continue asking and encourage your colleagues/associates to do so as well. – mickmackusa May 15 at 10:17
  • Thanks a lot ... you are so kind ... you done a real great job and i really recommend asking here for solutions. Stay healthy and take care! – Andreas Soraru May 15 at 14:27
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;
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
0

After working on that problem i figured out a solution with a little help and jsut wanted to share the solution to you.

$query = "SELECT sortb.value, sortb.field_id, sortc.value, sortc.field_id, 
          content.*, fieldvalues.* ";
          FROM  #__fields_values AS fieldvalues, #__content AS content
          LEFT JOIN (#__fields_values AS sortb)
          ON (content.id = sortb.item_id AND sortb.field_id=417)
          LEFT JOIN (#__fields_values AS sortc)
          ON (content.id = sortc.item_id AND sortc.field_id=46)
          WHERE content.catid=" . $catid . " AND content.id = fieldvalues.item_id 
          AND fieldvalues.field_id=418 AND fieldvalues.value LIKE '%" . $sorttype . "%' ";
          ORDER BY sortb.value, sortc.value;";

That solution works like a charm.

So thanks a lot to all who commented, stay healthy and safe!

2
  • Let me help you to avoid multi-joining the same large table by offering a pivot query. Please provide sample data. – mickmackusa May 12 at 8:32
  • What ever happened to 412 from your question ("release date")? What are 417 and 46? – mickmackusa May 14 at 9:31

Your Answer

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.