How to get names from another table by multiple ids

epifanios94 · February 23, 2021, 5:18pm

Hello,

I don’t know how to solve this problem:

I have two tables in the database:

-modx_decisions_posts:

Capture

-modx_violation_types_lists:

Capture2

As you can see in the modx_decisions_posts table I store the multiple violation types ID. In some snippet I want to get the name of violation types for showing in the frontend. The problem is getting only the first value of the row. For this example is showing only Type 1 GR. The multilingual values its ok, do not pay attention.

This is my snippet:

    <?php
    $output = '';

    if($cultureKey == 'el'){
        $cultureKey = 'gr';
    }
    $placeholder = $field.'_'.$cultureKey;
    $output = $modx->getPlaceholder($placeholder);

    $sql = "SELECT i.* FROM `modx_decisions_posts` AS q LEFT JOIN `modx_violation_types_lists` AS i ON (q.violation_type = i.id);";
    $result = $modx->query($sql);
    $res = $result->fetch(PDO::FETCH_ASSOC);
    $violation_types = $res["name_$cultureKey"];

    $output .= '<p>'.$violation_types.'</p>';
    return $output;

halftrainedharry · February 23, 2021, 5:05pm

You can’t do this with only one SQL query. You need multiple steps:

First read the violation_type from the table modx_decisions_posts

SELECT violation_type FROM modx_decisions_posts WHERE id = ...

Then replace the double pipe characters || with a comma and query the table modx_violation_types_lists

SELECT * FROM modx_violation_types_lists WHERE id IN (1,2)

Use fetch in a loop (or use fetchAll instead) to read multiple rows.

markh · February 23, 2021, 5:26pm

If these tables may grow into thousands of records (or more) of which you want to render a bunch of them at once, and you’re still in the development phase where you can change your database schema, then it may be worthwhile looking into a many-to-many table that lets you join directly. Rather than a violation_type field with pipe-separated values, you’d have a join table that links “decision_posts” with “violation types”. For large datasets being able of joining that directly is definitely a benefit.

An alternative suggestion: if you’re going to be iterating over a large number of decision_posts while the number of violation types is limited (eg < 100)… you can keep your database schema as-is, but before iterating over decision_posts you fetch all violation types into memory and simply load the matching types from an array.

If those two things don’t apply, what @halftrainedharry’s said.

bruno17 · February 23, 2021, 7:40pm

what @markh says… was allready suggested here:

epifanios94 · February 24, 2021, 3:29pm

@halftrainedharry I still have the same problem. can you see my code? Its showing only the first name


if($cultureKey == 'el'){
    $cultureKey = 'gr';
}
$placeholder = $field.'_'.$cultureKey;
$output = $modx->getPlaceholder($placeholder);


$sql = "SELECT violation_type FROM modx_decisions_posts";
$result = $modx->query($sql);
$res = $result->fetch(PDO::FETCH_ASSOC);

$eNum = implode(',',$res);
$e = "SELECT * FROM modx_violation_types_lists WHERE id IN ('$eNum')";
foreach ($modx->query($e) as $row) {
    $output .= $row["name_$cultureKey"];
}
return $output;

halftrainedharry · February 24, 2021, 4:02pm

First of all, this query

$sql = "SELECT violation_type FROM modx_decisions_posts";

together with one call to fetch returns the first row of the table modx_decisions_posts. So you probably want to select the right row with a WHERE clause.

$sql = "SELECT violation_type FROM modx_decisions_posts WHERE id = 1";

Then try replacing the line $eNum = implode(',',$res); with code like this

$eNum = str_replace('||',',',$res['violation_type']);

and delete the single quotes (') from the second query

$e = "SELECT * FROM modx_violation_types_lists WHERE id IN ($eNum)";

epifanios94 · February 24, 2021, 7:09pm

@halftrainedharry its working but only for the first row of the table modx_decisions_posts. If I insert another rows in the table modx_decisions_posts what I can do for showing the names of the ids?

halftrainedharry · February 24, 2021, 7:56pm

If you want to output all the rows in the table modx_decisions_posts, then call fetch in a loop.

$sql = "SELECT violation_type FROM modx_decisions_posts";
$result = $modx->query($sql);
while($res = $result->fetch(PDO::FETCH_ASSOC)){
    // put the code here that makes the sql query to modx_violation_types_lists 
}

epifanios94 · February 24, 2021, 8:08pm

I made this before but it was not work.

    <?php
$output = '';

if($cultureKey == 'el'){
    $cultureKey = 'gr';
}
$placeholder = $field.'_'.$cultureKey;
$output = $modx->getPlaceholder($placeholder);

$sql = "SELECT * FROM modx_decisions_posts";
$result = $modx->query($sql);

while($res = $result->fetch(PDO::FETCH_ASSOC)){
    $eNum = str_replace('||',',',$res['violation_type']);
    $e = "SELECT * FROM modx_violation_types_lists WHERE id IN ($eNum)";
    $out = array();
    
    foreach ($modx->query($e) as $row) {
        $out[] = $row["name_$cultureKey"];
    } 
}
    // do stuff
return implode(", ", $out);

bruno17 · February 24, 2021, 8:19pm

I think, you could do all of this with 2 nested migxLoopCollection - calls.
What is your xpdo - schema actually?

halftrainedharry · February 24, 2021, 8:30pm

You set the variable $out to an empty array in every loop $out = array(); and that overwrites all the content from the previous loop.

epifanios94 · February 24, 2021, 8:46pm

Schema (modx_violation_types_lists):

<?xml version="1.0" encoding="UTF-8"?>
<model package="violation_types_lists" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.0">
    <object class="Violation_Type_List" table="violation_types_lists" extends="xPDOSimpleObject">
        <field key="name_gr" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="name_en" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="name_tr" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="published" dbtype="int" precision="1" phptype="integer" null="false" default="1" attributes="unsigned"/>      
    </object>
</model>

Schema (modx_decisions_posts):

<?xml version="1.0" encoding="UTF-8"?>
<model package="decisions_posts" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.0">
    <object class="Decision_Post" table="decisions_posts" extends="xPDOSimpleObject">
        <field key="title_gr" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="content_gr" dbtype="text" phptype="string" null="false" default=""/>
        <field key="title_en" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="content_en" dbtype="text" phptype="string" null="false" default=""/>
        <field key="title_tr" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="content_tr" dbtype="text" phptype="string" null="false" default=""/>
        <field key="upload_pdf_gr" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="upload_pdf_en" dbtype="varchar" precision="255" phptype="string" null="false" default=""/> 
        <field key="upload_pdf_tr" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
        <field key="decision_number" dbtype="text" phptype="string" null="false" default=""/>      
        <field key="date" dbtype="datetime" phptype="datetime" null="false"/>       
        <field key="violation_type" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>       
        <field key="published" dbtype="int" precision="1" phptype="integer" null="false" default="1" attributes="unsigned"/>        
        <field key="date_search" dbtype="date" phptype="date" null="false"/>        
    </object>
</model>

bruno17 · February 25, 2021, 4:14pm

[[migxLoopCollection? 
&packageName=`decisions_posts` 
&classname=`Decision_Post` 
&tpl=`tplDecisionsPost` 
]]

chunk tplDecisionsPost:

[[+title_[[getCultureKey]]]]<br>


[[migxLoopCollection? 
&packageName=`violation_types_lists` 
&classname=`Violation_Type_List` 
&tpl=`@CODE:{{+name_[[getCultureKey]]}}`
&where=`{"id:IN":[ [[+violation_type:replace=`||==,`]] ]}`
&outputSeparator=`,` 
]]
<hr>

I don’t know, where your cultureKey is coming from, so I replace it with a custom - snippet, which you need to write yourself, to determine your cultureKey. If it is the MODX cultureKey, you could replace that snippet with the placeholder [[++cultureKey]]

and usually, you put all related classes/table into one xpdo - schema. Not one schema for each table

epifanios94 · February 25, 2021, 7:46am

@bruno17 Are displayed all violation types for every posts. Ok I will do it another way. I will change the type of column violation_type from the posts table and I will store the names of violation types directly, not the ids.

bruno17 · February 25, 2021, 9:04am

shouldn’t be the case. what do you get with &debug=1 in the second call ?

maybe try {{+violation_type}}

epifanios94 · February 25, 2021, 9:18am

Nothing. The results from the 2 nested migxloopcollection is this:

It return all violation types from all rows

And with &debug=1 it return this

bruno17 · February 25, 2021, 10:02am

ok, this {{+violation_type}} will not work, my mistake.

but, if you have a commaseperated list of ids within [[+violation_type]]
this should work

&where=`{"id:IN":[ [[+violation_type]] ]}`

what do you get with [[+violation_type]] alone in the tplDecisionsPost - chunk

epifanios94 · February 25, 2021, 10:10am

I get 1||21||2. Basically is showing the values only of the first row and double based on count of the rows for every post.

bruno17 · February 25, 2021, 10:42am

I was thinking, you had replaced the pipes with commas, you could try:

&where=`{"id:IN":[ [[+violation_type:replace=`||==,`]] ]}`

epifanios94 · February 25, 2021, 3:08pm

There is something wrong in the syntax here (&where={"id:IN":[ [[+violation_type:replace=||==,]] ]} ) because the color of my code in the editor is all white