Data is not rendered properly after new or edited row on joined tables

Data is not rendered properly after new or edited row on joined tables

elherztelherzt Posts: 14Questions: 3Answers: 0
edited March 2019 in Free community support

I am trying Datatables Editor this week, my CRUD is working well, I just have an issue that I can't fix.

When add new row or edit a row, server always return the first record of the table, data is saved properly on the db, if I refresh page I can see new data well, I don't know what is misssing... this is my code on server side.

I'm working with two tables, customer and customer_status, joined by customer_status_id in both tables.

var db = new Database("sqlserver", "myconnectionstring");
DtResponse response = new Editor(db, "customer")
                .Model<CustomerModel>("customer")
                .Pkey("customer_id")
                .Model<CustomerStatusModel>("customer_status")
                .Field(new Field("customer.customer_status_id")
                        .Options(new Options()
                            .Table("customer_status")
                            .Value("customer_status_id")
                            .Label("customer_status")
                            .Where(q =>
                                q
                                .Where("customer_status_id", 4, "=")
                                .OrWhere(r => r.Where("customer_status_id", 2, "="))
                                .OrWhere(r => r.Where("customer_status_id", 6, "="))
                            )
                        )
                    )
                .LeftJoin("customer_status", "customer_status.customer_status_id", "=", "customer.customer_status_id")
                .Where(q=>
                    q
                    .Where("customer.customer_status_id", 4, "=")
                    .OrWhere(r=> r.Where("customer.customer_status_id", 2, "="))
                )
                .Process(Request.Form)
                .Data();

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Is customer_id an identify column?

    Allan

  • elherztelherzt Posts: 14Questions: 3Answers: 0

    Yes, is the primary key of customer table.

  • elherztelherzt Posts: 14Questions: 3Answers: 0

    I have another examples with three joined tables working well, the unique different thing is the primary key, in others examples the primary key is "id", and this example primary key is "customer_id" Is this the problem?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    It shouldn't be since you've specified that in your Pkey() method call. You could try using:

    new Editor(db, "customer", "customer_id")
    

    instead, but it shouldn't really make any difference.

    That said - I might know the issue:

    .Model<CustomerModel>("customer")

    Try using:

    Pkey("customer.customer_id")
    

    i.e. prefix the table name.

    Allan

  • elherztelherzt Posts: 14Questions: 3Answers: 0

    I tried what you said, but I have the same result, always first record of the table. Data is saved properly.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you add .Debug(true) to your code (just before the .Process() call) and then show me the raw JSON return from the server please?

    Allan

  • elherztelherzt Posts: 14Questions: 3Answers: 0
    edited March 2019

    Hi Allan, I really appreciate the help you are giving me, this is the full response JSON on adding new customer:

    {
      "draw": null,
      "data": [
        {
          "DT_RowId": "row_3",
          "customer": {
            "customer_status_id": 4,
            "customer_id": 3,
            "customer": "MPI",
            "address": "473 E. Union St. Pasadena, CA 91101 (626) 395-7130",
            "city": "473e Union Pasadena2",
            "country": "USA",
            "alias": "MPI"
          },
          "customer_status": {
            "customer_status": "Active",
            "customer_status_id": 4
          }
        }
      ],
      "recordsTotal": null,
      "recordsFiltered": null,
      "error": null,
      "fieldErrors": [],
      "id": null,
      "meta": {},
      "options": {},
      "files": {},
      "upload": {
        "id": null
      },
      "debug": [
        {
          "Query": "DECLARE @T TABLE ( insert_id int ); INSERT INTO  [customer]  ( [customer_status_id], [customer], [address], [city], [country], [alias] ) OUTPUT INSERTED.customer_id as insert_id INTO @T VALUES (  @customer_status_id,  @customer,  @address,  @city,  @country,  @alias ); SELECT insert_id FROM @T",
          "Bindings": [
            {
              "Name": "@customer_status_id",
              "Value": 4,
              "Type": null
            },
            {
              "Name": "@customer",
              "Value": "New Customer",
              "Type": null
            },
            {
              "Name": "@address",
              "Value": "test",
              "Type": null
            },
            {
              "Name": "@city",
              "Value": "test",
              "Type": null
            },
            {
              "Name": "@country",
              "Value": "test",
              "Type": null
            },
            {
              "Name": "@alias",
              "Value": "test",
              "Type": null
            }
          ]
        },
        {
          "Query": "SELECT  [customer].[customer_id] as 'customer.customer_id', [customer].[customer_status_id] as 'customer.customer_status_id', [customer].[customer] as 'customer.customer', [customer].[address] as 'customer.address', [customer].[city] as 'customer.city', [customer].[country] as 'customer.country', [customer].[alias] as 'customer.alias', [customer_status].[customer_status] as 'customer_status.customer_status', [customer_status].[customer_status_id] as 'customer_status.customer_status_id' FROM  [customer] LEFT JOIN [customer_status] ON [customer_status].[customer_status_id] = [customer].[customer_status_id] WHERE [customer].[customer_status_id] = @where_0 OR([customer].[customer_status_id] = @where_2 )AND [customer].[customer_id] = @where_4 ",
          "Bindings": [
            {
              "Name": "@where_0",
              "Value": 4,
              "Type": null
            },
            {
              "Name": "@where_2",
              "Value": 2,
              "Type": null
            },
            {
              "Name": "@where_4",
              "Value": "76",
              "Type": null
            }
          ]
        }
      ],
      "cancelled": []
    }
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    It looks like it is the where statement that is going haywire there:

          WHERE
            [customer].[customer_status_id] = 4 OR (
                [customer].[customer_status_id] = 2
            )
            AND [customer].[customer_id] = 76 "
    

    Could you try this:

                    .Where(q=>
                        q.Where(r=> 
                              r.Where("customer.customer_status_id", 4, "=")
                              r.OrWhere("customer.customer_status_id", 2, "=")
                        )
                    )
    

    That should provide suitable grouping.

    Allan

  • elherztelherzt Posts: 14Questions: 3Answers: 0

    Finally the problem was solved, just a comment, I put:

    .Where(q =>
    q.Where(r =>
    r.Where("customer.customer_status_id", 4, "=")
    .OrWhere("customer.customer_status_id", 2, "=")
    )
    )

    I mean .OrWhere instead of r.OrWhere because it throw error.

    Thanks.

This discussion has been closed.