We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

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



77.7k
Accepted
answer 
$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


145.0k
edited Sep '17 
$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.



145.0k

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



7.3k

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)");


145.0k
edited Sep '17

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.