Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Get Multiple values from a relation

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 :)



36.8k
Accepted
answer
$catalogue = Catalogue::findFirst("link_rewrite = '{$rewrite}'");

NO NO NO

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
edited 20d ago
$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.

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

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)");
edited 18d ago

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.