Thread

This works, but fires an awful lot of queries.

If you need related information and you need to loop over a resultset, it’s usually best to add the required fields to the query using include_related().

Also note that Datamapper does not support threeway relations. Functionally it works fine, as you have noticed, but the relational logic can not deal with it.

Which means that if you delete a parent record, Datamapper will delete the relationship record that links the parent to the other tables. Regardless of the fact that the other two tables still need that record to define the relationship between them.

You have to be aware of this fact.

If you want to do this the safe way, include the relationship table as a normal table. Make a model for it, and define the relationship between the three models and the relationship model as one-to-many.

You’ll have to do something about your platform.

Even if this is going to work, DataMapper will not accept it. It needs the ‘id’ to be an integer, it uses casting (to int) or intval() at several places in the code.

Hmm.. this is going to be a problem.  PHP does not officially support 64bit and although you can compile for it, MS doesn’t have a 64bit PHP SQL driver.

#fail

How much tweaking do you think would be involved in the DM core?  Like, wouldn’t it be just changing the casting type to bigint..?  Everything else would fall into place.  (bigint would probably be for the best regardless).

*EDIT: note, the #fail is a PHP/MS #fail, not in reference to what you do!  lol.

Hmmm… So here you have an RDBMS that supports bigint’s, but don’t deliver a driver that can pass those to the application layer. Sounds like Microsoft…

If you run a standard $this->db query on that table, how are those bigints returned when using a 32bit environment? As strings?

Lol, yes, sounds like them.

I was going to try 2 things:

1) setup a system DSN on the web server and see how it passes the BIGINT through ODBC driver, but, not hopeful about that, b/c the limitation is PHP. But if I can get the (unsupported) 64 bit PHP binaries working w/ IIS and 64 bit ODBC driver, that should work.
2) as you suggested, a standard query should return ID as FLOAT. PHP is supposed to account for large numbers automatically.

The upshot is that this straw has broken the camels back and I may just get my 64 bit REHL box! YIPEE! *passes out cigars*

This works, but fires an awful lot of queries.

If you need related information and you need to loop over a resultset, it’s usually best to add the required fields to the query using include_related().

I am not sure where to put include_related()?  I would think I still have to select for each loop results.

Also note that Datamapper does not support threeway relations. Functionally it works fine, as you have noticed, but the relational logic can not deal with it.

Which means that if you delete a parent record, Datamapper will delete the relationship record that links the parent to the other tables. Regardless of the fact that the other two tables still need that record to define the relationship between them.

You have to be aware of this fact.

If you want to do this the safe way, include the relationship table as a normal table. Make a model for it, and define the relationship between the three models and the relationship model as one-to-many.

My 3 models (customer, equipment, manufacturer) has $has_many = array(‘customers_equipments’); and customers_equipments model has $has_one = array(‘customer’,‘equipment’,‘manufacturer’);  Is this what you mean to prevent the delete relationships?

Can you or someone please share with me the best way to use datamapper to recreated this query?  I don’t necessary need customer model just need to a where clause for customer_id

SELECT c.id, e.id, e.equipment_type, c_e.manufacturer_id, c_e.model_name FROM (customers c, equipments e) LEFT OUTER JOIN customers_equipments c_e ON c.id = c_e.customer_id AND e.id = c_e.equipment_id WHERE c.id=1;

results

+----+----+------------------------+-----------------+--------------+
| id | id | equipment_type         | manufacturer_id | model_name   |
+----+----+------------------------+-----------------+--------------+
|  1 |  1 | Pump                   |               1 | Turbo Jet    |
|  1 |  2 | Booster Pump           |               2 | Filter 10000 |
|  1 |  3 | Filter                 |            NULL | NULL         |
|  1 |  4 | Heater                 |            NULL | NULL         |
|  1 |  5 | Chlorinator            |            NULL | NULL         |
|  1 |  6 | Salt System            |            NULL | NULL         |
|  1 |  7 | Cleaner                |            NULL | NULL         |
|  1 |  8 | Blower Motor           |            NULL | NULL         |
|  1 |  9 | Control System         |            NULL | NULL         |
|  1 | 10 | Skimmer                |            NULL | NULL         |
|  1 | 11 | Light                  |            NULL | NULL         |
|  1 | 12 | Aux Pump               |            NULL | NULL         |
|  1 | 13 | Clean Filter Pressurer |            NULL | NULL         |
|  1 | 14 | Last Filter Clean      |            NULL | NULL         |
|  1 | 15 | Pool Capacity          |            NULL | NULL         |
+----+----+------------------------+-----------------+--------------+

To help those setting up DM from sparks, a small things to lookout for.

If you are using a case sensitive system like linux, make sure you fix the incorrect case for the extensions_path in the config/datamapper.php file.

Change from

$config['extensions_path'] = '../sparks/Datamapper-ORM/1.8.2/extensions';

to

$config['extensions_path'] = '../sparks/DataMapper-ORM/1.8.2/extensions';

I am not sure if the same typo is in github.

@Frank Wong,

Good remark, I’ll check.

@toadies,

Try something like

$c = new Customer();
$c->select('id')
    ->include_related('equipment', array('id', 'equipment_type'))
    ->include_related('customer_equipment', array('manufacturer_id', 'model_name'))
    ->where('id', 1)
    ->get();

@toadies,

Try something like

$c = new Customer();
$c->select('id')
    ->include_related('equipment', array('id', 'equipment_type'))
    ->include_related('customer_equipment', array('manufacturer_id', 'model_name'))
    ->where('id', 1)
    ->get();

Hello, thank you for the above code.
The above query only shows the 2 items logged in the customers_equipments table, but I want to show all the equipments that a customer could possibly have.  It would be blank(NULL) on the webpage.

Because DataMapper is about relations, is does a LEFT OUTER join. It isn’t made to retrieve stuff that isn’t related.

If you need this output, just add a custom method to your model:

public function mycomplexquery()
{
    return $this->query("put your SQL here");
}

As long as the query returns at least the id column of the current model table, this will work fine, and your object will be populated as usual.

Will DataMapper work with an EAV table? If so how would I setup the relationships.

Depends on how you implement EAV.

In the end, it’s about tables, so as long as you obey the DataMapper rules for key fields (id for the primary key, <something>_id for foreign keys), you’ll be fine.

Thanks. Only 1 table will be EAV unless I can come up with a better way. My concern is with searching against multiple items in the table.

That is always an issue if you store EAV tables in a relational database. Perhaps a noSQL platform will suit you better?

That is always an issue if you store EAV tables in a relational database. Perhaps a noSQL platform will suit you better?

Not really an option. We are rebuilding an application that needs to run on your standard hosting package. I really want to go away from the EAV structure but we need to give the users the ability to add custom fields to the db ad they need to be searchable.