Using soft deletes can lead to mysql ambiguous exception

[niteraven7]

If two tables use soft deletes and you join them together the deleted field becomes ambiguous.

For example (I believe this query is created for pagination):

SELECT COUNT(*) AS numrows
FROM recipient
INNER JOIN location ON recipient.location_id = location.id
WHERE location.business_id = 3
AND deleted = 0

deleted should be recipient.deleted to correct the issue.

version: beta1

[atishhamte]

Can you provide the CodeIgniter code which generates this query?

[atishhamte]

@lonnieezell and @jim-parry, I have done some analysis on the code of pagination and db drivers. Found, paginate works with single model only, so there wont be chances of having join there. If we use $this->db->join, we can directly state the table name with column name to avoid ambiguous column issue.

[lonnieezell]

@atishamte I don't think that's quite true about not having issues with paginate. If you would do a join with another table that has a deleted_at field, and included all fields from both tables you could run into problems, I believe.

Should be as simple as adding the table to the deletedField during the constructor, but I think we might run into issues with binding.