Get the list of article and related specific custom field value

Question

I need to compose a list for a RSFormPro form field which contains options that display article titles and their respective date from a custom field.

In front something like this :

Select your courses :
Title of the article - 25/12/2019 (date from the custom field)

I see this article, but I don't know how combine it with the custom field www.rsjoomla.com/support/documentation/r...st-from-a-table.html

With the following code, I am able to retrieve article title from specific categories

// Prepare the empty array
$items = array();
// Prepare the database connection
$db = JFactory::getDbo();
// Keep this if you'd like a "Please select" option, otherwise comment or 
remove it
$items[] = "|Please Select[c]";

// Run the SQL query and store it in $results
$db->setQuery("SELECT title, title FROM jos0x_content WHERE catid = 13 or 
catid = 14");
$results = $db->loadObjectList();

// Now, we need to convert the results into a readable RSForm! Pro format.
// The Items field will accept values in this format:
// value-to-be-stored|value-to-be-shown
// Eg. m|M-sized T-shirt
foreach ($results as $result) {
    $value = $result->title;
    $label = $result->title;
    $items[] = $value.'|'.$label;
}

// Multiple values are separated by new lines, so we need to do this now
$items = implode("\n", $items);

// Now we need to return the value to the field
return $items;

In database :

Table jos0x_content : title
Table jos0x_fields_values : field_id=2(calendar field), value, and 
  in item_id the related id of the article 

Answer 1

It is basically a LEFT JOIN to relate the tables and WS_CONCAT() calls to prepare the output strings within the SQL (no extra php handling of the result set). If EVERY article has a date value in the fields_values table, then you can use innerJoin(); if you use innerJoin() and there is no related row in the joined table for a specific article, then that article will not be represented in the result set.

Because I am using a "glue" to concatenate with Joomla's concatenate() method, the output is not prone to breaking on NULL fv.value values.

I am using nested concatenation because I need two different "glues". The inner concatenation provides pipe-separated text like The Title|The Title. The outer concatenation appends space-hyphen-space then the formatted date string like The Title|The Title - 28-11-2019.

In case you want to apply some sorting to the query, I've arbitrarily decided to sort by article date in descending order -- feel free to play with that per your needs.

For the record, ALL of the $db->qn() calls can be safely removed from my script. Joomla endorses the use of its quoting methods to err on the side of keeping their websites secure and stable. However, all of your tables, columns, and their aliases will run just as smoothly without being backtick wrapped. (...as I have explained here, here, here, here, and here based on the concrete criteria discussed here.) You will notice a profound increase in script readability upon removing these unnecessary quoting methods.

This is not tested and it is entirely possible that I've made mistakes. If so, please don't be shy about commenting under my answer; I require all of my answers to work as desired.

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true);
    $query
        ->select(
              $query->concatenate([
                  $query->concatenate([
                      $db->qn("c.title"),
                      $db->qn("c.title")
                  ], "|"),
                  "DATE_FORMAT(" . $db->qn("fv.value") . ", '%d-%m-%Y')"
              ], " - ")
          )
        ->from($db->qn("#__content", "c"))
        ->leftJoin($db->qn("#__fields_values", "fv")
              . " ON " . $db->qn("c.id") . " = " . $db->qn("fv.item_id"))
        ->where([
              $db->qn("fv.field_id") . " = 2",
              $db->qn("c.catid") . " IN (13,14)",
              $db->qn("c.state") . " = 1")
          ])
        ->order($db->qn("fv.value") . " DESC");
    $db->setQuery($query);
    return "|Please Select[c]\n" . implode("\n", $db->loadColumn());
} catch (Exception $e) {
    return "|Syntax Error";
}

Additions based on commented requirements:

• use MySQL's DATE_FORMAT() to customise your datetime stamp.
• to include only published articles in the result set, add $db->qn("c.state") . " = 1" as another element in where()'s array.