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

elherzt

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();

allan

Is customer_id an identify column?

Allan

elherzt

Yes, is the primary key of customer table.

elherzt

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?

allan

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

elherzt

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

allan

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

elherzt

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": []
}

allan

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

elherzt

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.