Join #__content with #__fields_values then filter and sort using #__fields_values data

Question

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.

Answer 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());

Answer 2

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;

Answer 3

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!