Problem with adding join tables
Problem with adding join tables
Hello everyone,
After buying Editor our company has been working with datatables for an internal application. The data we're trying to show is selected from three different tables, namely 'countries', 'brands' and 'modules'. The row in 'brands' contains a foreign key 'country_id' and the one to one relationship between brands and modules is as follows: 'modules' has a 'brand_id' foreign key.
We were able to show the data with simply using the ->leftJoin() method as it was mentioned in the docs. However, when we try to add a new 'brand' and with it a row within the table 'modules', it gives a foreign key constraint error. This is as logic dictates, but if any of you could give us some input as to how we might be able to solve this problem it would be very appreciated.
This week and the last we've been trying to use the ->join() method, but without success. The error we're currently struggling with is:
sError: "Table selected fields (i.e. '{table}.{column}') in Join
must be read only. Use set(false)
for the field to disable writing."
This question has an accepted answers - jump to answer
Answers
Hi,
Thanks for the details of the problem you are having. Could you show me the PHP you are using for where you have Editor to setup to insert into both
brand
andmodules
?Editor will insert / update on the primary table first, and then on the joined table. So if you require the information to be in the joined table first, you would indeed get a foreign key error. We could change that by reordering the data slightly, but you wouldn't have the foreign key to be able to insert that into the primary table.
The other option is to invert the tables, so your primary table becomes the
modules
table and thebrand
is the joined table.Regards,
Allan
Hi Alan,
Thanks for your quick answer!
I've done what you said but I'm getting this error now when I try to add a new one:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (
analytics
.modules
, CONSTRAINTmodules_brands_id_brands_brand_id_restriction
FOREIGN KEY (brand_id
) REFERENCESbrands
(brands_id
))Allan said:
Here is what I have right now:
```
<?php > ``` ?><?php
error_reporting(0);
error_reporting(E_ALL);
ini_set('display_errors', 1);
Just so I understand fully, your form is submitting information for both the brands table and modules table at the same time and expecting a new row to be inserted into both. Is that correct?
Typically I would expect individual database tables to be edited individually. The issue with inserting into both in the same form submission is that the new row id from the first inserted row (
modules
) isn't available available to the second (brand
). Normally that second value would be selected by the end user.What you probably would have to do in this case is use the
Join
class rather thanleftJoin
as you suggested before, as it does have access to the inserted row id. The error you were seeing before suggests that there was a misconfiguration in theField
instances in theJoin
class (they shouldn't have a.
in their names).Allan
Hi Allan,
Your first assumption is correct. We are using a single form to enter a new brand with its corresponding row from the 'modules' table to avoid one giant table.
Your second paragraph describes the problem we've been having perfectly. That's exactly what we're having trouble with.
Your third paragraph seems to be on-point as well. Because our tables all have the column 'id', we tried using the following:
modules
.id
. From what I understand, though, that isn't a viable way of doing this?What would you recommend we do, exactly? Maybe we just need an example that would be applicable to our situation just so we can wrap our heads around this :)
Thank you very much for your input so far.
That is only on the client-side, but on the server-side in the
Join
you would just useField::inst( 'id' )
. The one-to-many documentation might be of some help.If you have problems with that, could you show me the PHP you are using for it?
Interestingly this is the first use case I've found for using the
object
Join type...! The .NET libraries don't provide that option and it was included only in the PHP libraries because theleftJoin
method wasn't introduced until v1.3.One for me to think about moving forward!
Regards,
Allan
Hi again Allan,
We've been trying to follow your instructions, but keep hitting the same error. After we changed our server-side code to the following:
and our client-side datatables code to this:
We keep getting "Datatables warning: unknown field 'brand_name'." Do you have any insight that might be able to help with this further?
We're amazingly grateful to your help in this matter, I do not know if we could have gotten this far on our own.
Regards,
Are you expecting it to be one-to-one, or one-to-many?
array
toobject
brands.city
would bebrands[].city
Also
{ data: "brand_name" },
should refer to the joined data -brands.brand_name
for example, or justbrands.name
if you remove the alias (again, taking into account the above about one-to-one or one-to-many).Allan
Hi Allan! Thanks for your support so far.
Prepare, because this will be a long question, though probably also the final one before we start getting the results we want.
As of now, this is our php:
And this is our javascript:
I'm glad to announce that adding and editing records works. But the bad news is that deleting them doesn't work. When we select a row and click "delete", only the row in the table 'brands' gets deleted and the row in 'modules' just stays there. This causes "empty rows" in datatables.
They look like this: http://i.imgur.com/IpjN8yG.jpg
Also, we haven't been able to get the actual country name in the "country" table. It just shows the ID.
I think once we figure out what to do here this thread could be very helpful to people in the future.
Thanks again! Hope to hear from you soon.
Edit: it should also be mentioned that the error we got before "Datatables warning: unknown field 'brand_name'." was gone after we simply started the columns object within the initialization of DataTables with an item from 'modules'. We then hide it with responsive datatables' "never" class and who knows, we might need to use the id some day :).
Just a little update:
We got the delete to work. It turned out to be an error that came with version 1.4.1. I will mark this comment as the answer, but if you're reading this in the future and are actually looking for the answer to this question, you'll have no choice but to read the entire thread.
Hi,
Gosh, sorry I lost track of this thread! Excellent to hear that the update to the latest version now has everything working the way it should!
Regards,
Allan