Link Table is not being populated, what am I doing wrong?

Varshney

I've been trying to figure this out for a while but can't seem to put my finger on it. I currently have a database structure as follows (with fake data):

-------------------    -----------------    -------------------
|     reviews     |    |    images     |    | reviews_images  |
-------------------    -----------------    -------------------
| id_rev |  name  |    | id_img | url  |    | rev_id | img_id |
-------------------    -----------------    -------------------
|   1    | Doom   |    |   1    | http |    |   2    |   1    |
|   2    | Doom 2 |    |   2    | http |    |   2    |   2    |
|   3    | Doom 3 |    |   3    | http |    |   2    |   3    |
-------------------    -----------------    -------------------

When I add a new record to the list it adds the records for both the reviews and the images table. For some strange reason the link table (reviews_images) does not get update and is left completely blank. Once the tables loads the new data I have the review data showing but with the blank spot where the image should be.

As the image table is being populated (and the file subsequently uploaded to the correct directory), I assume that the link table is working and doing its job during processing, but why it isn't during data injection I don't know. This is what I have for my server script (irrelevant parts left out):

Editor::inst($db, "reviews", "id_reviews")
    ->fields(
        Field::inst("reviews.id_reviews"),
        Field::inst("reviews.name")                 -> validator("Validate::notEmpty"),
        Field::inst("reviews_images.reviews_id")    -> options("reviews", "id_reviews", "id_reviews"),
        Field::inst("reviews_images.images_id")     -> options("images", "id_images", "id_images"),
        Field::inst("images.id_images")             -> setFormatter("Format::ifEmpty", null)
                                                    -> upload(Upload::inst($_SERVER["DOCUMENT_ROOT"]."/uploads/__ID__.__EXTN__")
                                                        -> db("images", "id_images", array(
                                                            "filename"      => Upload::DB_FILE_NAME,
                                                            "filesize"      => Upload::DB_FILE_SIZE,
                                                            "web_path"      => Upload::DB_WEB_PATH,
                                                            "system_path"   => Upload::DB_SYSTEM_PATH
                                                        ))
                                                        -> validator(function($file) {
                                                            return $file["size"] >= 500000 ?
                                                                "Files must be smaller than 500KB" :
                                                                null;
                                                        })
                                                        -> allowedExtensions(array("png", "jpg", "bmp", "gif"), "Please upload an image")
                                                    )
    )
    ->leftJoin("reviews_images", "reviews.id_reviews", "=", "reviews_images.reviews_id")
    ->leftJoin("images", "images.id_images", "=", "reviews_images.images_id")
    ->process($_POST)
    ->json();

Any ideas on what could be causing the issue from this? If need be my SQL syntax is the following (and works 100%):

// Loading data
SELECT
    reviews.id_reviews,
    reviews.name,
    images.url
FROM reviews
LEFT JOIN reviews_images
    ON reviews.id_reviews = reviews_images.reviews_id
LEFT JOIN images
    ON images.id_images = reviews_images.images_id

allan

With a link table, you would need to use a many join in order to be able to update the link table when submitting data. You can certainly read data using the method above with two left joins, but for writing it, the Mjoin would be needed.

Another option perhaps is to do away with the link table and just put an image id column into your reviews table.

Allan

Varshney

Sorry for the late response, I have only just been able to get back to this. So far I've been trying to use Mjoin with a similar database structure where a link table is required. What I have done is this:

-------------------   -----------------   ------------------------
| GAMES           |   | GAMES_SERIES  |   | GAMES_HAS_SERIES     |
-------------------   -----------------   ------------------------
| ID | NAME       |   | ID | NAME     |   | GAMES_ID | SERIES_ID |
-------------------   -----------------   ------------------------
| 1  | BioShock   |   | 1  | BioShock |   | 1        | 1         |
| 2  | BioShock 2 |   | 2  | C&C      |   | 2        | 1         |
-------------------   -----------------   ------------------------

Editor::inst($db, "games")
    -> field(
        Field::inst("games.id"),
        Field::inst("games.name") -> Validator("Validate::notEmpty")
    )
    -> join(
        Mjoin::inst("games_series")
            -> link("games.id", "games_has_series.games_id")
            -> link("games_series.id", "games_has_series.series_id")
            -> fields(
                Field::inst("id")
                    -> options("games_series", "id", "name"),
                Field::inst("name")
            )
    )
    -> process($_POST)
    -> json();

(function(S) {
    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor({
            ajax: "table.games.php",
            table: "#records",
            fields: [
                { label: "Game Name:", name: "games.name" },
                {
                    label: "Add to series?:",
                    name: "games_series[].id",
                    type: "select"
                }
            ]
        });

        $("#records").on("click", "tbody td:not(:first-child)", function (e) {
            editor.bubble(this);
        });

        var table = $("#records").dataTable({
            dom: "Bfrtip",
            ajax: "table.games.php",
            columns: [
                { data: "games.id" },
                { data.games.name },
                { data: "games_series", render: "[, ].name" }
            ],
            order: [[1, "asc"]],
            select: true,
            lengthChange: false,
            buttons: [
                { extend: "create", editor: editor },
                { extend: "edit", editor: editor },
                { extend: "remove", editor: editor }
            ]
        });
    });
}
(jQuery));

What is currently happening is that when adding a record I can view the form and create the record on the GAMES table and select the GAME SERIES that are available to choose from. The issue is the same as before unfortunately. The game is added to the GAMES table but the GAMES_HAS_SERIES table is not updated.

When having inline editing enabled I can edit each column on the table fine until I get to the part where I click on the Series column where I get an error like this:

Uncaught:
Unable to automatically determine field from source. Please specify the field name.

As I'm able to show the data in the select field I would have thought this would work? Is there something here that I might have done wrong that you can point out?

The strange thing is that if I change the SELECT to a CHECKBOX then the form works by adding to the GAMES_HAS_SERIES table, but I still have the issue of inline editing with the same error message above.

allan

What is the games_has_series table mentioned in your link()s? It isn't in the ascii art tables.

Unable to automatically determine field from source. Please specify the field name.

Detailed information about that error is available here with instructions on how to resolve it.

Allan

Varshney

Sorry I've edited the above now. Thanks for the link, that worked perfect when using a CHECKBOX field type. However I want to try and get this working under a SELECT field type as there will be hundreds of different series.

If anyone want to know what I did I changed...

{
    data: "games_series",
    render: "[, ].name"
}

...to this:

{
    data: "games_series",
    render: "[, ].name",
    editField: ["games_series[].id"]
}

When using a SELECT it does show up but it just refuses to save for some reason.

allan

Can you give me a link to the page? I'd like to have a look at what it is sending to the server please.

Regards,
Allan

Varshney

I'm currently developing it locally, but I did just try uploading it all to a test server and for some reason I'm getting a user password error. I'm going to try and fix it.

Edit: It's now working. You can try it out here.

allan

I think the issue might be that the data being submitted is just a plain value, rather than an array of multiple values, which would be expected for a one-to-many join.

Try adding: multiple: true to your select field configuration object and hopefully that will fix it.

Allan

Varshney

Setting multiple: true works but as soon as I set multiple: false it will stop working again. I have also tried (without multiple) using a radio field type too and that also doesn't save any changes. It seems that a checkbox and select field types with multiple: true only works.

{
    label: "Add to series?:",
    name: "games_series[].id",
    type: "select",
    multiple: true
}

allan

It seems that a checkbox and select field types with multiple: true only works.

If you are using it with an Mjoin, then yes. You would want to use it as an array which implicitly suggests multiple: true.

Allan

Varshney

Thanks for that answer, it explains a lot. I think now that means I have figured out my options and what needs to be done regarding even the first post, but I'll give that a try sometime this week and get back to you.