Donate
Is it possible to create a custom SQL query (specifically, a pivot table) and treat its results like you treat tables with FuelPHPs ORM?
I have an SQL statement that pivots a table for me. The image below shows what the pivot SQL statement does. On the left we have the 'properties' table and on the right we have the result of the SQL pivot statement below.
SQL Pivot Statement Results: 
SQL Pivot Statement:
SELECT
item_id,
MAX(IF(property_name = 'color', value, NULL)) AS color,
MAX(IF(property_name = 'size', value, NULL)) AS size,
...
...
...
FROM
properties
GROUP BY
item_id;
Question:
Can I execute the above statement, then access the columns through the normal ORM methods such as
echo $table->color;
$table->color = 'blue';
$table->save();
Also, I looked at FuelPHP EAV and it looks like it might be what I need... but I couldn't get it working. Is it what I need?
I got the code above from a buysql.com tutorial on pivot tables. It does exactly what I need but not sure how to integrate with ORM.
I got something "working" for my purposes, let me know if there is a better way. See the original post here.
1) Make the sql create a temporary table.
CREATE TEMPORARY TABLE IF NOT EXISTS temp_compiled_properties AS
(SELECT
item_id,
MAX(IF(property_name = 'color', value, NULL)) AS color,
MAX(IF(property_name = 'size', value, NULL)) AS size,
...
...
...
FROM
properties
GROUP BY
item_id);
2) Create a Model
//this is not a typical ORM model and cannot be accessed as such
//You must first run the init function before data can be accesses
//You can read data from the model, other methods may work, but not all ORM functions have been tested with this model.
class Model_Properties_Pivot extends \Orm\Model
{
//--------------------------------------
//Table Details
//--------------------------------------
protected static $_table_name = 'temp_compiled_properties';
protected static $_primary_key = array('item_id');
//dont define properties, ORM will automatically query the temp table to get these
//protected static $_properties = array();
public static function init()
{
$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_compiled_properties AS
(SELECT
item_id,
MAX(IF(property_name = 'color', value, NULL)) AS color,
MAX(IF(property_name = 'size', value, NULL)) AS size,
...
...
...
FROM
properties
GROUP BY
item_id)";
DB::query($sql)->execute();
}
3) Read the data in the Model
Model_Properties_Pivot::init();
$all_data = Model_Properties_Pivot::query()->get();
Also, I looked into EAV and got it working. Issue is my "attribute" field is actually another foreign key (auto increment number). That means I'm unable to use it since it's an auto increment number. Basically, I can't access it like this $properties->10
It looks like you're new here. If you want to get involved, click one of these buttons!
