I am building a takeaway finder that will find takeaways within a set distance of a uk postal code. What will happen is the user puts his/her postcode in an input box and clicks submit, the site then searches for takeaways near the user. But the catch is that this search is based on the individual takeaways delivery distance. So if a takeaway has a delivery distance of say 8 miles and the persons postcode is within 8 miles of the takeaway it will show in the results.

So far I have uk postcode database with lang and lat coordinates and also the takeaway database table holds the takeaways own postcode and its delivery distance but not the long and lat values of the takeaways postcode.

I have following sql query but im not sure how to implement something like this using datamapper?

Also would’nt mind a bit of advice on setting up the logic of the above takeaway finder

If things get this complicated, create a method in your model, run a standard $this->db query, and convert the returned result set back to DataMapper objects:

Do this only if the query is run on the models table, and if the result set includes at least the ‘id’ column.

WanWizard - 17 January 2012 03:21 AM

Your first solution is possible, but dangerous. DataMapper only knows the foreign key, but has no way of knowing to which parent table the record belongs. When selecting, you can use where_related() as a filter on ‘type’, but be careful when deleting related records, it will delete all records with a given foreign key.

Join tables is a better solution, it will keep your relations clean.

Thanks for your help! I ended up doing a bit of minor re-engineering in the db to use join tables which looks a lot cleaner.

Now i have a ‘blocks’ table with no parent_id or type columns. I then have my lessons and responses tables and join tables between those and the blocks table. This app is likely to grow and have other objects which require blocks related to them so it’s just a matter of creating that new join table.

I’ve got a question that I can’t find in the documentation.

The situation is as follows. I have a table (B) that has two connections (1 and 2) to the same other table (C).

Situation:

table A
id
b_id

table B
id
c1_id
c2_id

table C
id
d_id

table D
id
code

Now I am trying to use a where_related, and tables B and C are in the middle if the query. How do I force it to take route 1 or 2 in the joins?

$t->where_related(‘A/B/C/D’,‘code’, ‘x’)

Is this possible when using the where_related at all?

I’ve got a new issue, which could be a bug (or feature).
There are three models, they have the following relations:

A has one B (can be null)
A has many C
B has many A
B has many C
C has one B
C has one A

I fill this table by:
1. importing A without reference to B,
2. importing B and C
3. Update A with the references to B.

At step 3 datamapper generates strange queries, where it updates all other A’s with NULL that have the same B as the one we are about to add to the current A.

[6] => UPDATE `A` SET `B_id` = NULL WHERE `A`.`B_id` =  8 AND `A`.`id` <> 1
[7] => UPDATE `A` SET `B_id` = 8 WHERE `id` =  1

Is this behaviour as it should be? It seems to be enforcing that I only use a B once with an A.

You can only define parallel relations using advanced relationships. And you have to name them differently. You will have to use that name when you run the query, not the name of the class.

As to your second question, that should only happen if it is a one-to-one relation (i.e. both sides of the relation use has_one).

Thanks, my first question is now solved

Concerning the second, There is no one to one relation defined on both sides, I just checked.

What is the code you use to perform step 3? So what exactly generates those SQL statements?

I checked the datamapper code, but it only does that when:
- the relationship with the related object is an has_one
- the current object has the foreign key

It does not check the other side of the relationship, so it interprets the has_one here very strict.

Update the relation from the other side to avoid that:

The code that generates the “query” (using datamapper of course) is generic, as we are importing multiple CSVs to the database, where one CSV can have multiple target (or source) tables. We have devised a language to allow insertion of many to many, one to many and many to one relations. One to one we did not need.

Of course I could see this as a many to one relation from B, and we have some code making that possible. Tomorrow I will take a look if that approach works.

Thanks again for your help! I’ve never seen a guy so devoted to support!

Hmmmm. I just removed table C out of the equasion, and then it saves without any problems, although
- the relationship with the related object is an has_one
- the current object has the foreign key

A has one B (can be null)
//A has many C
B has many A
//B has many C
//C has one B
//C has one A

Strange… It seems that the presence of table C enforces a reciprocal 1 to 1 relation between A and B. Is that possible?

Edit: After removing the relations and adding them back in, it is working! I compared to the latest in the SVN-repository, and there are no changes. Twilight zone!

Probably something went wrong with copying to the test environment…

Security and URI Segments

Hi, folks!

I would like to ask you if DM have some treatment when URI segments are passed as arguments. For example:

It’s safe to use code like this or I have to do some other thing?

I’ve found a small bug in the code. One of my tables is called “group”. When joining this table, this results in a MySQL-error, as it results in the following SQL:

LEFT OUTER JOIN `group` group ON `group`.`id` = `group_vehicle`.`group_id`

The table group is given the alias “group” without the quotes.

I fixed it by changing line 4579 of /application/library/datamapper.php

from:

to:

and line 4618 from:

to:

Maybe this is not the best way to fix this, but for me it works (and I do not have much time

tarciozemel - 26 January 2012 07:48 AM

I would like to ask you if DM have some treatment when URI segments are passed as arguments.

No, DM doesn’t touch your data. You wouldn’t be happy if it did.

You could opt to add a validation rule on that field that sanitizes all data saved in that column.

@yoast,

This is a bug in Codeigniter, not in Datamapper.

It is the job of the join() method to escape it’s identifiers. Which it does for the table name, but not for the alias.

I suggest you report this as a bug.

Will do. Thanks.