Hidden row?

[MGA555]

I have deployed a OpenJS Grid grid on a website. In doing final testing, I noticed that one of the rows is hidden. I have a basic left join which appears to return all the appropriate records. and the pager at the bottom reports the correct number of records, but only one (should be 2) that meet the left join criteria (both are from the same left table record) is displayed.

I couldn't find anything related to hiding rows, but I am pretty sure that is what is happening.

Any suggestions?

[MGA555]

Working with a second query, and having the same problem. I can tell that the MySQL query has returned the correct number of records, but many of them are hidden. How can I make the hidden rows visible? The rows that are hidden, and not in the html for the page, if that any help.

[optikalefx]

Do you see a hidden row in the grid? if you inspect it with Chrome Dev tools? The "hidden" row thing was taken out. Now the top row just has a margin top. I wonder if you have a style that's causing the margin-top not to be set

div.gridWrapper .columns .cell:nth-child(2), div.gridWrapper .columns .cell.topMargin {
  margin-top: 40px;
}

[MGA555]

I don't think it is a style issue because the values for the cells are not in the HTML at all. Each page has a few rows at the top, but underneath that is a big blank area. I have set if to display 25 rows if that matters.

I am using Firefox (my Chrome is broken), and inspecting source the data is not there. Should I be looking at anything else?

[MGA555]

I might be clearer if I explained that the rows are more like missing than hidden, except for the fact that each page "says" it has the 25 rows, but only a few of them are actually displayed.

[optikalefx]

if you look at the ajax call in the console (network panel) are the correct number of rows in the data object? Or are they not even being returned from ajax.php properly?

[MGA555]

The pager information shows 3 rows, and although I am weak on this information, to me, the console data is only for the 2 rows that are displayed.

I'm not sure if this image will show up, but here goes:

POST http://localhost/botest/admin/reports/$0Orders-ajax.php?startDate=2013-4-4&endDate=2013-4-4

{"rows":{"_34779":{"custorder_id":"34779","ordersid":"88847","order_status":"placed","order_total":"0
.00","price":"45.00","name":"Alexander Rachmiel","prodname":"Berkshire Basket
3-21 & 3-22, 2013 -
custom"},"_35102":{"custorder_id":"35102","ordersid":"","order_status":"placed","order_total":"0.00"
,"price":"","name":"Nicole Woodard","prodname":""}},"nRows":3,"order_by":"ordersid","sort":"DESC","page"
:"1","start":1,"end":25,"colData":[],"saveable":{"0":"custorder_id","2":"order_status","3":"order_total"
}}

There should be a second string for custorder_id 34779, with a different ordersid, so including the one for custorder_id 35102, a total of three reows should be displayed. So you were correct in thinking the ajax.php is not suppllying the information.

Here is an excerpt of my ajax:

$grid = new Grid("custorder", array(
"save"=>true,
"delete"=>false,
"where"=>"order_total='0' and delivery_date >= '$startDate' and delivery_date <= '$endDate'",
"joins"=>array(
"left join orders on (custorder.custorder_id=orders.custorder_id)",
"left join address on (custorder.user_id=address.id)"
),
"fields"=>array(
"ordersid" => "orders.orders_id",
"price" => "orders.price",
"name" => "CONCAT(address.firstname,' ',address.lastname)",
"prodname" => "orders.prod_name"
),
"select" => 'selectFunction'
));

I tried various combinations of single quotes around the table name (based on your example), but got no display at all for every variant I tried.

I'm not sure what the last part of data is, but I'm guessing it is general grid information, and not row information. It looks like it knows there were 3 rows, even though the data was passed for only 2 of them.

thanks for your help

[MGA555]

In case this fell off your radar screen, just a polite reminder that I am still stuck on this.

thanks

[optikalefx]

There can't be 2 with the same ID of 34779. Javascript objects have unique keys. So, if your IDs that you're using are not unique, that would cause this problem. Normally we want the primary key to be used because it's set as unique.

If you're making an orders table, then the ID should be the order id, and not the customer id. What is your primary key set to on your orders table? The way we figure out the primary key is by running this internally mysql_query("SHOW KEYS FROM$tableWHERE Key_name = 'PRIMARY'");

[MGA555]

I'm pretty sure the table structure is set up correctly. The custorder table primary key is custorder_id. The orders table (which contains the line items for an order) primary key is orders_id, but in another field the custorder_id is located, This allows me to join the tables as I have done, to get a separate row for each of the line items of each of the customer orders. In this case there are 2 line items for custorder_id 34779, and one for custorder_id 35102. Using the joins as described above in PHPMyadmin, I get the desired results, in this case the three rows, but not with OpenJS Grid?

[MGA555]

I don't doubt that I am doing something wrong, but just can't figure out what it is.

[optikalefx]

Do you want a grid of customers, or orders?

[MGA555]

I'm not sure how to answer that. I want a grid of orders, but listing the custorder ID (which is the "order" number, and the order_id (which is the line items), and the customer name.

So basically for this simple 3 line example I expect:
Custorder_ID Orders_ID
34779 88847 .....
34779 89739 .........
35102 Null ........

The fact the the Orders_ID is Null for the third line is exactky why I am making this grid, to identify all Customers that do not have a record in the Orders table.

When I run the query with PHPMyadmin, I get the expected results. When I use OpnJS Grid, configured as above, I only get 2 rows in the grid. I am sure I am doing something incorrectly, but can't figure out what it is.

[MGA555]

I just changed the database so that the second order actually has a line item. I still only get 1 row for the custorder_ID of 34779. So it is like for every custorder_ID i will only get 1 row, no matter how many orders have that ID in its custoreder_id filed.

[optikalefx]

What is your primary key for table "custorder".

My guess here is that because you're selecting from that table, your primary key is customer_id which is why you can't have duplicate customer ids (which your result set has)

Your solution is to change your starting table from custorder to order. and then Join back on customer. This way order id is unique.

If you want to send me your SQL file and query that works, i can take a better look at how to set the query up. But this is definitely the problem right now. (duplicate keys)

[MGA555]

I may have inadvertently broken some MySQL rule, but I did not think I had. Here are the tables and the primary keys:

Table Key
custorder custorder_id
orders orders_id

Since there are 0 to N entries in the orders table for any given custorder_id, the join of those 2 tables will have records that have duplicate custorder_id's. There can be multiple records with the same custorder_id (corresponding to having to 2 or more line items in an order). However, treating the order_id as the primary key for the join will not work either, because there may be multiple orders with a null for the order_id. So the join does not actually have a primary key, does it need to?

I'd be glad to send you a stripped down database, and working query if that will help, just tell me where to send it.

I suspect that you have identified the issue, and am thinking that OpenJS Grid wants to use the primary key of the first table listed in a join as a primary key for the "table" that is the result of the join. Is that correct?

At this point I do not see how I can meet that requirement. Even if I start the query with the orders table, I would have to do a right join so all the records in the custorder table will show up, even if their orders_id is NULL. That would leave the join with many records with a orders_id that is NULL, which I presume will also be problematic for OpenJS Grid?