FuelPHP » Forums

Table views and their columns

Oggo87 February 2019

Let's say I have a class Model_Formula that extends ORM\Model. The class sets the following:

protected static $_table_name = 'glaze_formulas';

protected static $_properties = array(
    'id',
    'colour_id',
    'date',
    'best_spray_weight',
    'description',
    'notes',
    'created_at',
    'updated_at',
);

protected static $_views = array(
        'glaze_formulas_tot_quantity' => array(
                'columns' => array(
                        'tot_quantity'
                ),
        ),
);

Then in my controller I have the following query:

Model_Formula::query()->use_view('glaze_formulas_tot_quantity')
		->where('id',$id)->get_one();

In my view I want to access the column defined in the 'glaze_formulas_tot_quantity' table view, what how do I get to do that? If I try to do my usual $formula->tot_quantity, I get an undefined index Exception. Am I missing something?

Harro February 2019

I have never used Views in combination with the ORM, but that should work.

I've looked at the hydrating code, and that just puts all results from the record in an array, which is then fed to Model::forge(). And all fields in the data array will become properties, either because they are in the record, or they are custom properties added.

Have you checked the profiler, to see if the query does return that column, i.e. if it is defined (properly) in the view?
Oggo87 February 2019
I'm running some testing in my sandbox environment, and printed out both the query and the object resulting after the query execution. The query seems to be correct (I've shortened it to include only the relevant fields)
```
SELECT `t0`.`id` AS `t0_c0`, (...) `t0`.`tot_quantity` AS `t0_c8`
FROM `glaze_formulas_tot_quantity` AS `t0` WHERE `t0`.`id` = 1
```
The object also has the tot_quantity field in the _custom_data array
```
[_custom_data:protected] => Array
	(
		[tot_quantity] => 4
	)
```
So, I started looking at the ORM/Model::get method, and turns out it's trying to return the field from the _data array
```
$result = $this->_data[$property];
```
while it should be returning it from the _custom_data array
```
$result = $this->_custom_data[$property];
```
After that change, the field is returned correctly.
BTW, I'd like to point out that I'm running on 1.8.1 and I don't know if this was already fixed in 1.9.
Harro February 2019
Accepted Answer

After a quick look you might be right.

Anything in _data[] must be defined in the models properties, and is checked and caught first. So by the time you get to the code in get() that deals with view data, it can't be in _data anymore.

Which in turn means that the entire view elseif can go, as the next elseif will check the property against the custom_data array anyway.

https://github.com/fuel/orm/commit/4ed84e051fc31f76c0c09c8c3944e6aed31b7d24

Something else I've spotted. You can save() a model object created from a view. Which imho is very wrong, as it could well be the view doesn't contain the PK, or any other relevant fields for that matter. I'll dealt with that to.

https://github.com/fuel/orm/commit/b556e63373a4dd47ecc14a5246684d0d6e12d809
Oggo87 February 2019
Fantastic, I'm glad my digging helped improving Fuel even just a little bit! :)

By the way, and in a somewhat related note, it seems to be impossible right now to use table views in related ORM Models. Sticking to my case above, I have a Model_Colour with a has-many relationship to Model_Formula. It would be a nice feature to be able to relate to my glaze_formulas_tot_quantity view doing something like this:
```
$query = Glazes\Model_Colour::query()
	->related('formulas')
	->use_view('formulas.glaze_formulas_tot_quantity');
```
I looked into to the code a little bit and it looks like it might require a little bit more thinking and coding than what I can dedicate to it right now. Just throwing it out there as a possible enhancement, and I might open an issue for the request.
Harro February 2019

No, views are (what ORM is concerned) an afterthought.

And not very popular, since you've bumped into an issue above that has been present in the code since "custom_data" was introduced, back in 2013. Appearently nobody uses views (in combination with the ORM).

I saw your issue about this, but I think implementing it might be quite complex.

From an architectural point I'm not so happy with this anyway. A model should be a one-to-one mapping with a database table. Or view (which is like a virtual table).

So I would be more inclined to remove all the view code currently in the ORM, and instead say that for every view, a model should be defined, as the database doesn't care if TESTa and TESTb are tables or views in this example:

SELECT * from TESTa LEFT JOIN TESTb on TESTa.key = TESTb.key.

which would translate to

$result = Model_TESTa::query()->related('TESTb')->get();

This also means the ORM doesn't care, in ordering, relating, etc.
Oggo87 February 2019
I see what you mean, and it does make sense. Mine was just a mere suggestions, looking at how things work right now.
BTW, there's a mistake in your save() fix, it should be
```
if ($this->_view)
```
and not
```
if ($this->view)
```
Harro February 2019

Saw it as soon as I pushed. Which was just to late. Immediately fixed. ;-)

Add a Comment

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

Harro February 2019
Oggo87 February 2019

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion