Get Multiple values from a relation

Question

Get Multiple values from a relation

costapombo1997 Aug '17

Created Aug '17	Last Reply Sep '17	Replies 4	Views 649	Votes 0

costapombo1997 7.3k

Aug '17

Hello,

My Catalogue model:

    $this->hasManyToMany(
        'id',
        'Core\Models\categoriesToCatalogue',
        'catalogue_id',
        'categorie_id',
        'Core\Models\Categories',
        'id',
        array('alias' => 'categories')
    );

Categories Model :

   $this->hasManyToMany(
        'id',
        'Core\Models\categoriesToProduct',
        'category_id', 'product_id',
        'Core\Models\Products',
        'id',
        array('alias' => 'products')
    );

    $this->hasManyToMany(
        "id",
        "Core\Models\AttributeGroupsToCategory",
        "category_id",
        "attr_group_id",
        "Core\Models\AttributeGroup",
        "id",
        array('alias' => 'attributes_group')

Is there anyway to make the following code work ?? I need to get all "attributes_group" and "products"

$catalogue = Catalogue::findFirst("link_rewrite = '{$rewrite}'");
$attr_groups = $catalogue->getCategories()->attributes_group;
$products = $catalogue->getCategories()->products;

Thank you in advance :)

Lajos Bencz
77.7k

Accepted
answer

Sep '17

$catalogue = Catalogue::findFirst("link_rewrite = '{$rewrite}'");

Never use parameters as literal strings, you should always use binded parameters:

$catalogue = Catalogue::findFirst(['conditions'=>'link_rewrite=:rewrite:'], 'bind'=>['rewrite'=>$rewrite]]);
// or simply:
$catalogue = Catalogue::findFirstByLinkRewrite($rewrite);

As to your question:

$catalogue->getCategories(); // this returns an array, because it's N:N relationship
// so you cannot access it like this:
$attr_groups = $catalogue->getCategories()->attributes_group;

// instead, you have to process it in a loop:
$attr_groups = [];
$products = [];
// same as $catalogue->getCatagorues()
foreach($catalogue->categories as $category) {
    foreach($category->attributes_groups as $attr) {
        $attr_groups[] = $attr;
    }
    foreach($category->products as $prod) {
        $products[] = $prod;
    }
}

You could, of course, write helper methods on the Catalogue model:

class Catalogue extends \Phalcon\Mvc\Model
{
    public function getAttributesGroups() {
        $groups = [];
        foreach($this->categories as $category) {
            foreach($category->attributes_groups as $attr) {
                $groups[] = $attr;
            }
        }
        return $groups;
    }
}

And then in you controller:

$catalogue = Catalogue::findFirstByLinkRewrite($rewrite);
$attr_groups = $catalogue->getAttributesGroups();
// $catalogue->attributes_groups will NOT work

Wojciech Ślawski · Answer 1 · 2017-09-01T03:56:14-07:00

$attr_groups = $modelsManager->createBuilder()
    ->columns('AttributeGroup.*')
    ->from(['AttributeGroup.'=>'Core\Models\AttributeGroup'])
    ->innerJoin('Core\Models\AttributeGroupsToCategory', 'AttributeGroupsToCategory.attr_group_id = AttributeGroup.id', 'AttributeGroupsToCategory')
    ->innerJoin('Core\Models\categoriesToCatalogue', 'AttributeGroupsToCategory.category_id = categoriesToCatalogue.category_id', 'categoriesToCatalogue')
    ->innerJoin('Core\Models\Catalogue', 'categoriesToCatalogue.catalogue_id = Catalogue.id', 'Catalogue')
    ->where('Catalogue.link_rewrite = :link_rewrite:', ['link_rewrite' => $rewrite])
    ->getQuery()
    ->execute();

And similar with products. If you care about performance it's best way to do it imho.

Wojciech Ślawski · Answer 2 · 2017-09-01T04:05:27-07:00

Wojciech Ślawski
145.0k

Sep '17

Well i added not needed join there, 3 is enough i think :D

costapombo1997 · Answer 3 · 2017-09-03T06:52:28-07:00

first of all, thank you everyone specialy Lajos Bencz for his super detailed answer. However, your solution requires several "foreach and like Wojciech Ślawski said i do care about performance.

Before reading our solutions, I wrote the following code

    $sql = "SELECT AttributeGroup.id AS attr_group, products.id AS productsids  FROM categories_to_catalogue AS categoriesToCatalogue " .
        "LEFT JOIN attribute_groups_to_category AS AttributeGroupsToCategory ON AttributeGroupsToCategory.category_id = categoriesToCatalogue.categorie_id " .
        "LEFT JOIN attribute_group AS AttributeGroup ON AttributeGroup.id = AttributeGroupsToCategory.attr_group_id " .
        "LEFT JOIN categories_to_Product AS categoriesToProduct ON categoriesToProduct.category_id = categoriesToCatalogue.categorie_id " .
        "LEFT JOIN products AS products ON products.id = categoriesToProduct.product_id " .
        "WHERE categoriesToCatalogue.catalogue_id = {$this->view->catalogue->id}";

    $data = $this->db->query($sql);
   /* there is no id=0, but this will allow-me to use {% if product.count() > 0%} on my view in case there are no products or groups*/
    $groups = $products = "0";

    while ($a = $data->fetch()) {
        $groups .= $a['attr_group'] ? ",".$a['attr_group'] : '' ;
        $products .= $a['productsids'] ? ",".$a['productsids'] : '';
    } 

    $this->view->attribute_groups = AttributeGroup::find("id IN ($groups)");

    $this->view->products = Products::find("id IN ($products)");

Wojciech Ślawski · Answer 4 · 2017-09-03T13:05:40-07:00

Well still imho better use PHQL and query builder. It looks kind of better. Also please use binding still, even it's value from view which you possibly are sure it's int, still better use binding.