display data from related table when foreign key is an array

to

tomkat 25 Jan, 2016

Hi,

How to display in CC5 data when got 2 tables connected as:

table1, cols as: id,user, cars, ...
table2, cols as: id,car, ...

and in table1 in col cars got ex: 1,4,6

in table2 accordingly for table1, for above example, that would be records: Fiat, BMW, Honda

and wish to have finally in CC5 listing something like:

John | Fiat, BMW, Honda | ....
Peter| Fiat | ....

Could you point me some idea how to achieve this?

Regards,

Gr

GreyHead 26 Jan, 2016

Hi tomkat,

My first suggestion would be that you don't save the second table like that - instead have multiple records for each use e.g.

John | Fiat
John | BMW
John | Honda

That's better design and makes it easier to use.

With your existing design, using LIKE in the conditions box should let you identify the records: WHERE `table2.col2` LIKE '%BMW%'
Bob

to

tomkat 29 Jan, 2016

Hi Bob,

Thank you for answer. That really pointed me out how to get proper data, however for me works sth like this below in join conditions:

 
return array( ':table1.cars IN (table2.id)');

But still got the problem, that it's return just the first value from the string (ex from (3,4,7) just 3) . I expect need to use explode ? but can't figure out how. In join condition/condition, that is not working for me, or more possibly i do it wrong🙂.

Tom

Gr

GreyHead 30 Jan, 2016

Hi Tom,

If you turn the Debugger on do you see what data you are getting from your query? It's hard to know exactly what is happening from the info here.

Bob

to

tomkat 01 Feb, 2016

From the debuger I see sth like

[tab1] => Array
{
[cars] => 3,4
.....

[tab2] => Array
{ 
[id] => 3
[car] => BMW
}

and what I wish to get is car with id 3 and 4, in one cell for the list, and accordingly for every record in tab1, all records for cars in tab2

so sth like

John | BMW, Honda
Peter | Honda, Fiat, Dacia
john | Honda

So possibly I should use explode funcion , but don't know how to use it properly according to CC rules

Tom

Gr

GreyHead 01 Feb, 2016

Hi Tom,

Is this the right way round return array( ':table1.cars IN (table2.id)'); Shouldn't that be return array( ':table2.cars IN (table1.id)');Or are we getting table names confused here?

If your tab 2 listing has all the cars in it then I guess that you could look up he names from the IDs but it does seem to me that the logical next step might be to re-think the table design if it is proving quite so complex :-(

Bob

to

tomkat 02 Feb, 2016

Hi Bob,

Sure, you are right, actualy I use code as follow
return array( ':table2.id IN (table1.cars)');
but anyway it's not working, it's return just first value.
About redesigning tables, you are probably right, but i got them ready and filled with data (6 tables all together in relations) from the old component.
I thought there is some smart trick to display it quicly, as becoause, i see there is some funcionality similar in CF5. I mean when you use dropdown, and set 'multiple' in form designer to 'yes' that saves data exactly the same way in DB, as 1,2,4 in one cell, so thought there is quick way to display it in CC.

Regards

Gr

GreyHead 02 Feb, 2016

Hi Tom,

You can use explode() to convert a comma separated string back into an array - but I'm not sure that helps enough here as you need to do the conversion somewhere in the database queries that are loading your data.

Bob

to

tomkat 16 Feb, 2016

Ok, so for every one who struggle with this too. The main wrong point was to try to find correct relation using CC rules. Easier was just to treat second table with cars as an external database just for name storage. Then was quite simple to use joomla rules to display readable names to users,

so for example in front list -> action -> view code as


$temp=explode(",",$this->data['table1']['cars']); 
reset($temp);
foreach ($temp as $value) {
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
   ->select('car')
    ->from($db->quoteName('#__your_dbname'))
    ->where($db->quoteName('id') . ' LIKE '. $db->quote($value));
$db->setQuery($query);
$results = $db->loadResult();
echo "$results<br />\n";}

same code but using proper $cell and $rows variables could be used in frontlist -> settings -> php fuunctions,

than only if you wish to filter by the single value from that field (let's stay with cars still) using LIKE not =, you need more code, but this topic was usefull.
http://www.chronoengine.com/forums/posts/f12/t99477/dynamic-search-filtering-in-cc.html?hilit=%22filter+like%22

Forums

display data from related table when foreign key is an array