Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Create a pivot table and access it from ORM?
  • I originally posted this on stackoverflow, but there doesn't seem to be much of a FuelPHP presence there. Could someone give me a hand with this? Here is the original post on stackoverflow, I find it easier to read than on this forum. 


    Question

    Is it possible to create a custom SQL query (specifically, a pivot table) and treat its results like you treat tables with FuelPHPs ORM?


    Example

    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: On the left we have the 'properties' table and on the right we have the result of the following SQL pivot.

    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

  • HarroHarro
    Accepted Answer
    EAV isn't a pivot table, it's an attribute-value container, which means it contains single combinations of an attribute and it's value, and not a list. The attribute is available as virtual property of the model object.

    So your EAV for a specific model object could contain:
    color => blue
    weight => 64

    which you can access via $object->color and $object->weight. If your attribute is a foreign key, you've got a design issue (from an EAV point of view) or you want something that can't be addressed with EAV.

Howdy, Stranger!

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

In this Discussion