Mapper and Virtual Field

[AndrewMarkUK]

I have a mapper set up like so...

class ProductsModel extends DB\SQL\Mapper
{
public function __construct(DB\SQL $db)
{
parent::__construct($db,'products');
}
public function all(): array
{
$this->category_name = 'SELECT categories.name AS category_name FROM categories LEFT JOIN products p on categories.id = p.category_id';
return $this->load();
}
}

Based on using virtual fields as suggested in the docs, I am trying to get a category_name from another table (categories) related as per the left join above. However, nothing is returning, no category_name is appearing in data.

return $this->load(); is working fine...I can see the columns from the products table as configured in the construct.

I am perhaps misunderstanding the purpose of virtual columns. Can anyone provide me with a hint on this.

Thank you in advance!

[Rayne]

What does the logged command look like (if it is logged)?

[pauljherring]

My impression is that virtual fields should be individual snippets from the documentation at https://fatfreeframework.com/3.7/sql-mapper#VirtualFields . SUM(score) and MAX(score) being given there. You appear to be inserting a whole query where a column would be expected? (But I'd expect an error from the underlying resultant query, unless it's being deliberately omitted.) Alternatively put parentheses round the SELECT and add an alias?: $this->category_name = '(SELECT categories.name AS category_name FROM categories LEFT JOIN products p on categories.id = p.category_id) as category_name'; Another alternative would be to create a view which produces the data you're after based upon the two tables, and query the view instead.

…

On Sat, May 14, 2022 at 6:29 PM Andrew Mark ***@***.***> wrote: I have a mapper set up like so... class ProductsModel extends DB\SQL\Mapper { public function __construct(DB\SQL $db) { parent::__construct($db,'products'); } public function all(): array { $this->category_name = 'SELECT categories.name AS category_name FROM categories LEFT JOIN products p on categories.id = p.category_id'; return $this->load(); } } Based on using virtual fields as suggested in the docs, I am trying to get a category_name from another table (categories) related as per the left join above. However, nothing is returning, no category_name is appearing in data. return $this->load(); is working fine...I can see the columns from the products table as configured in the construct. I am perhaps misunderstanding the purpose of virtual columns. Can anyone provide me with a hint on this. Thank you in advance! — Reply to this email directly, view it on GitHub <#1254>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA6B4LQKH3JTBOVQVX3UZFDVJ7PHFANCNFSM5V53GT6A> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- PJH

[AndrewMarkUK]

I think I am a step closer. For the sake of clarity, I am including the print_r output.

ProductsModel Object
(
[db:protected] => DB\SQL Object
(
[uuid:protected] => 156syof287mb3
[pdo:protected] => PDO Object
(
)

        [dsn:protected] => mysql:host=localhost;port=3306;dbname=machinecompare
        [engine:protected] => mysql
        [dbname:protected] => machinecompare
        [trans:protected] => 
        [rows:protected] => 2
        [log:protected] => (6.7ms) SHOW columns FROM `machinecompare`.`products`

(0.3ms) SELECT id,name,quantity,category_id,(SELECT c.name FROM categories c WHERE c.id = products.category_id) AS category_name FROM products

    )

[engine:protected] => mysql
[source:protected] => products
[table:protected] => `products`
[as:protected] => 
[_id:protected] => 
[fields:protected] => Array
    (
        [id] => Array
            (
                [type] => int(11)
                [pdo_type] => 1
                [default] => 
                [nullable] => 
                [pkey] => 1
                [auto_inc] => 1
                [value] => 1
                [initial] => 1
                [changed] => 
                [previous] => 1
            )

        [name] => Array
            (
                [type] => varchar(255)
                [pdo_type] => 2
                [default] => 
                [nullable] => 
                [pkey] => 
                [auto_inc] => 
                [value] => Product One
                [initial] => Product One
                [changed] => 
            )

        [quantity] => Array
            (
                [type] => int(11)
                [pdo_type] => 1
                [default] => 
                [nullable] => 
                [pkey] => 
                [auto_inc] => 
                [value] => 10
                [initial] => 10
                [changed] => 
            )

        [category_id] => Array
            (
                [type] => int(11)
                [pdo_type] => 1
                [default] => 
                [nullable] => 
                [pkey] => 
                [auto_inc] => 
                [value] => 3
                [initial] => 3
                [changed] => 
            )

    )

[adhoc:protected] => Array
    (
        [category_name] => Array
            (
                [expr] => (SELECT c.name FROM categories c WHERE c.id = products.category_id)
                [value] => Category Three
                [initial] => Category Three
            )

    )

[props:protected] => Array
    (
    )

[query:protected] => Array
    (
        [0] => ProductsModel Object
            (
                [db:protected] => DB\SQL Object
                    (
                        [uuid:protected] => 156syof287mb3
                        [pdo:protected] => PDO Object
                            (
                            )

                        [dsn:protected] => mysql:host=localhost;port=3306;dbname=machinecompare
                        [engine:protected] => mysql
                        [dbname:protected] => machinecompare
                        [trans:protected] => 
                        [rows:protected] => 2
                        [log:protected] => (6.7ms) SHOW columns FROM `machinecompare`.`products`

(0.3ms) SELECT id,name,quantity,category_id,(SELECT c.name FROM categories c WHERE c.id = products.category_id) AS category_name FROM products

                    )

                [engine:protected] => mysql
                [source:protected] => products
                [table:protected] => `products`
                [as:protected] => 
                [_id:protected] => 
                [fields:protected] => Array
                    (
                        [id] => Array
                            (
                                [type] => int(11)
                                [pdo_type] => 1
                                [default] => 
                                [nullable] => 
                                [pkey] => 1
                                [auto_inc] => 1
                                [value] => 1
                                [initial] => 1
                                [changed] => 
                                [previous] => 1
                            )

                        [name] => Array
                            (
                                [type] => varchar(255)
                                [pdo_type] => 2
                                [default] => 
                                [nullable] => 
                                [pkey] => 
                                [auto_inc] => 
                                [value] => Product One
                                [initial] => Product One
                                [changed] => 
                            )

                        [quantity] => Array
                            (
                                [type] => int(11)
                                [pdo_type] => 1
                                [default] => 
                                [nullable] => 
                                [pkey] => 
                                [auto_inc] => 
                                [value] => 10
                                [initial] => 10
                                [changed] => 
                            )

                        [category_id] => Array
                            (
                                [type] => int(11)
                                [pdo_type] => 1
                                [default] => 
                                [nullable] => 
                                [pkey] => 
                                [auto_inc] => 
                                [value] => 3
                                [initial] => 3
                                [changed] => 
                            )

                    )

                [adhoc:protected] => Array
                    (
                        [category_name] => Array
                            (
                                [expr] => (SELECT c.name FROM categories c WHERE c.id = products.category_id)
                                [value] => Category Three
                                [initial] => Category Three
                            )

                    )

                [props:protected] => Array
                    (
                    )

                [query:protected] => Array
                    (
                    )

                [ptr:protected] => 0
                [trigger:protected] => Array
                    (
                    )

            )

    )

[ptr:protected] => 0
[trigger:protected] => Array
    (
    )

)

So...no errors now BUT, the field (category_name), which is now returning the correct value (thank you @Rayne and @pauljherring) for the pointer is not coming through under fields but under adhoc. This is (perhaps?) the intended behaviour...but how to retrieve that value so I can include it in a template.

For the other fields (in a template, I am just using ((@row.id}}, {{@row.name}} etc and they are appearing in the template but {{@row.category_name}} is not showing / rendering...and no error...like, it's actually not there.

I must be nearly there in figuring this? :-)

[AndrewMarkUK]

BTW...this is my updated model that pushes the above...

public function all($f3)
{
    $this->category_name='SELECT c.name FROM categories c WHERE c.id = products.category_id';
    return $this->load();
}

[AndrewMarkUK]

OK. So actually, this is now working. Seems I need to remove cache...when I did this...the value appeared in the table rows. Thanks to everyone for tips though...all helped!

[Rayne]

That's good news. Don't forget to close the issue. :-)