XF 2.2 Getting data from multiple tables

[XboxUnlimited]

Hi, I need to get data from multiple database tables and display them in the same template, not sure of the best way of going about it.

I have DB table1

Table 1

• productID
• descriptions

Table 2

• productID
• productOptions

So I need to get the productID from table 1 and find the matching productID in table 2 and display the productOptions from table 2, there are multiple options in table 2 for the same productID so I need to loop through and list all the matching productOptions.

I was thinking of maybe a foreach loop within a foreach loop or something like that, not sure if that would work in XF.

Any suggestions of the best way of doing this? I'm sure it's simple once you know how.!!

 

[asprin]

If I understood you correctly, what you need to do is create a TO_MANY relationship between your entities by defining it in the entity class that these tables represent.

After that, when you fetch the Product via the finder object, you can dot walk inside a template to get the associated product options.

 

[XboxUnlimited]

I couldn't get the TO_MANY to work as it gave an error, TO_ONE kind of worked as I could get then get one of the productOptions displayed from Table 2 via the relationship between entities however I still don't know how I would loop through and display all the productOptions.

* I have got it working without using relationships by doing another fetch() on table 2 and looping through that, but I doubt it's the correct/most efficient way of doing it *

 

[briansol]

TO_MANY doesn't actually work from everything i've read and tried.

What you did is what i have done...

$mainproduct= $this->getMainRepo()->findProduct()->where('slug', $params['slug'])->fetchOne();
           
 $relationproduct = $this->getrelationRepo()->findRelatedproduct()->where('id', $mainproduct->id)->fetch();
                    
            $viewParams = [
                
                'pagetitle'         => $pagetitle,            
                'data'              => $mainproduct,    
                'relationproduct'    => $relationproduct,
               
            ];

and then in the template,
iterate/print through $data like normal

and then show the relations like:

<h2>Related items</h2>                                            
                    
                            <ul>
                                <xf:foreach loop="$relationproduct" value="$r">
                                    <li><a href="{{ link('xyz', {$r}) }}">{$r.name}</a></li>
                                </xf:foreach>
                            </ul>

 

[XboxUnlimited]

Thanks to all for the advice on here, I have got this part working now.

Next lesson is to work out how routing works !!