Rayne
commented
2 years ago What does the logged command look like (if it is logged)?
Closed AndrewMarkUK closed 2 years ago
Rayne
commented
2 years ago What does the logged command look like (if it is logged)?
pauljherring
commented
2 years ago 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 https://github.com/bcosca/fatfree/issues/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
commented
2 years ago 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
I must be nearly there in figuring this? :-)
AndrewMarkUK
commented
2 years ago 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();
}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
commented
2 years ago That's good news. Don't forget to close the issue. :-)
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!