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?

[optikalefx]

can you send me those 2 tables in a SQL file? sean@square-bracket.com

[MGA555]

I sent you the sample tables, and some thoughts/observations last Thurs and Fri AM. I hope you got them, but am posting this in case they went to a junk mail/Spam folder.

thanks

[optikalefx]

Are you Mike (interWeb)? If so, yea they were in junk. Glad you told me!

[MGA555]

Yes, that;'s me! Sorry I did not mention that. I thought the subject line would make things clear.

[MGA555]

Do you have any insight on the problem I am having?

[optikalefx]

Hey Mike, sorry I haven't got to your issue yet. Work is keeping me busy.

From just a minute looking at this, your query seems a bit off to me. I would have wrote this one

SELECT *
FROM orders o
LEFT JOIN custorder c on o.custorder_id = c.custorder_id

Which translates to

new Grid("orders",[
    "joins" => ["LEFT JOIN custorder c on o.custorder_id = c.custorder_id"],
    "fields" => ["c.custorder_id","o.orders_id","o.price","c.user_id", "o.order_total"] 
]);

This gives the grid a primary key to use, which is order_id

[MGA555]

You overlooked the issue, that I would need a right join, so as to capture any Custorder which does not have an Order. That results in multiple records with a Null orders_id, and thus no primary key on the join.

It looks to me that the query is OK (I conclude that because the "pager" at the bottom of the grid reports the correct number of rows), but that the issue is caused when the MySQL results are assigned to an array, when the MySQL id is used for the array index. If that is what is happening, can the values be assigned to the array, using an arbitrary, independent index?

[optikalefx]

So if you wanted to show null order rows I would then suggest this query

SELECT *
FROM custorder c
LEFT JOIN orders o on o.custorder_id = c.custorder_id
GROUP BY c.custorder_id

So I would then do

new Grid("custorder",[
    "joins" => [LEFT JOIN orders o on o.custorder_id = custorder.custorder_id"],
    "fields" => ["custorder.custorder_id","o.orders_id","o.price","custorder.user_id", "o.order_total"],
    "groupBy" => "custorder.custorder_id"
]);

And the result is http://cl.ly/image/242g1U2O3U3k Which would show 5 rows in OpenJS Grid

[optikalefx]

The problem you keep coming to is that you have duplicate IDs for what OpenJSGrid thinks is your primary key. And the way the Javascript data is display is in a JS object.

{
    _1: {},
    _2: {}
}

You can't have

{
    _1: {},
    _1: {},
    _2: {}
}

The result of this above in JS is http://cl.ly/image/1J0e2S130F0P An object with only 2 keys. Not 3. Because the _1s duplicate. In JS you can't have duplicate keys to an object - which is how OpenJS works, it sets the object keys as the primary key values. I hope that makes sense.

[MGA555]

Thanks for your insight and suggestions.

The use of Group by might be a good workaround for my needs in this simple case. However, I have other needs where I guess reversing the tables in the Join would work, when there aren't any NULL ids.

But I can see getting a headache having to be careful that the join has a unique index, or missing a record if there happens to be a NULL ID or 2.. I don't claim to be an expert, but in all the work I have done, I never needed one.

I only half understand your explanation about JS. I am not seeing why you can't assume the join has no primary key, and just assign an arbitrary one just as MySQL does when you auto assign an index (every time a record is created, the auto index is incremented).

It seems in general a dangerous and limiting assumption that a join in fact has a primary key. I don't think there is a database convention that requires it. Am I mistaken?

[MGA555]

I just thought of this:

In MySQL, the Cross Join is the Cartesian Product, and by definition will not have a unique index. Although I have yet to need such a join, they do exist and must fill a need. In any event, I think that indicates that in general, a Join may not have a unique index.

[optikalefx]

It is true that in mySQL you don't have to a unique index. The reason OpenJSGrid wants one is because of something in Javascript called Object Direct Access. That isn't an official term, just something I'm using to describe the situation.

In Javascript, if you have an array that is say

[{}, {}, {}]
``

That is to say, an array of objects. The problem is, what if you wanted to work with the 5th element? Well you could assume index 4 is the 5th element. But what if the rows are moved around? What if a new row is added? What if a row is deleted? All of a sudden your reference to the 5th element is wrong. You need to figure a new index.  

Going through a huge array looking for data is slow. (If you have a lot of data)

Now if you had an Object instead of an Array.

var obj = { "sean": {}, "joe" : {}, "bob" : {} }

Now if I wanted to access something for joe, i can jump straight to obj.joe. Javascript can DIRECTLY access this part of the HUGE object now.  This is exactly how mySQL indexes work too. It creates a lookup in memory that is keyed by an index that you can use to directly access content. In this manner, you don't have to loop and look for the data you want, you can directly jump to it.

OpenJSGrid uses this concept. And the keys that it chooses to use are the unique IDs of the rows themselves.  OpenJS Grid could in a different world make up fake unique IDs and not use the ones from mySQL, but it serves double duty as I know that is also the ID used for updating the row in a save call.

The downside to this method as you've seen, is that you need your result set to have unique IDs. 

[MGA555]

Thanks for the explanation, which I will have to study to fully grasp. What is the criteria that OpenJS-Grid uses to determine what it will use as the Key? Is it different depending on whether the key is displayed on the grid, or not displayed?

.

[optikalefx]

This guy https://github.com/optikalefx/OpenJS-Grid/blob/master/grid.php#L518 It literally asks for the primary key of the table.

[MGA555]

So that function is called for the table specified in the "From" statement?

Could I work around my problems by forcing that key to be something other than the tables primary key?

If I put some logic in the function, can I specify the primary key to be from a different table, e.g can the returned value be of the form "return $primaryKey['Different Table.Column_name'];"?

[optikalefx]

If you don't need saving or deleting then yea you can. But of course you need the unique IDs in order to save/delete.