jqGrid with mORMot

warleyalex · 2013-04-11 20:44:22

I recently spent a few hours building a quite simplistic project using jqGrid with mORMot (remote paging, sorting and multi-searching). jqGrid demos examples are very handful and very fast!

jqGrid with mORMot - Part 1
Click here to see my 1-minute video
http://youtu.be/Z6-fWPdW9WM

jqGrid with mORMot - Part 2
Click here to see my 3.5-minute video
http://youtu.be/CB8p8eW4pvM

-----------------------------------------------------------------

Now, I would like to test a "master-detail" child grid, or even a subgrid. But I don't know how can I perform this task in mORMot.

Example:
Click on a row (Main grid) to see all orders for this customer (child grid), when end-users select a row and will fire the RowSelecting event handler (send the parameter customerId) and databind a second grid with details.

Main Grid
=========
SELECT customerID, companyName, ContactName, Phone, City FROM customers

Child Grid
==========
SELECT ordered, requiredDate, shipName, shipCity, freight FROM orders where customerID=? ORDER BY ordered desc

Any examples, ideas?

Last edited by warleyalex (2013-04-21 22:25:33)

ab · 2013-04-12 09:46:49

Thanks for the video.

Just use the RESTful URI corresponding to it, with a WHERE=... parameter to specify something like "customerID=:(1234): ORDER BY ordered desc".

warleyalex · 2013-04-19 20:11:30

I'm trying to create a basic example Master detail with subgrid embeded.
The idea was using models to just define and map database records for consuming JSON content via AJAX. So, I converted an existent MS SQL Northwind database to sqlite to work with jqGrid and mORMot.

Technically everything goes pretty well, except for Foreign Keys. I realized that (or at least my understanding of it), an 'ID INTEGER PRIMARY KEY' field is always created first, but don't create Foreign Keys between tables. I can not define others primary key. Firstly, I have to import data and then create by hand both primary key and Foreign key using a Administration tool.

I have a dirty idea to copy some implemented ideas by the TMS Aurelius to work with mORMot to create PK and FK. Consider the following classe with respective mapping, it creates the fields but no success with Foreign Keys.

===================================================
FIELDNAME	PRIMARY KEY	FOREIGN KEY
----------------------------------------------------------
OrderID		PK1          FK2 (Orders.OrderID)
ProductID       PK2          FK1 (Products.ProductID)
UnitPrice
Quantity
Discount
-----------------------------------------------------------

  TSQLOrderDetails = class(TSQLRecord)
  private
    [Association([TAssociationProp.Lazy, TAssociationProp.Required], [])]  
    [JoinColumn('OrderID', [TColumnProp.Required], 'OrderID')]
    FOrderID: Proxy<TSQLOrders>;      
    [Association([TAssociationProp.Lazy, TAssociationProp.Required], [])] 
    [JoinColumn('ProductID', [TColumnProp.Required], 'ProductID')]    
    FProductID: Proxy<TSQLProducts>;  
    [Column('UnitPrice', [TColumnProp.Required])]
    FUnitPrice: double;               
    [Column('Quantity', [TColumnProp.Required])]
    FQuantity: integer;               
    [Column('Discount', [TColumnProp.Required])]
    FDiscount: double;                
    function GetOrderID: TSQLOrders;
    procedure SetOrderID(const Value: TSQLOrders);
    function GetProductID: TSQLProducts;
    procedure SetProductID(const Value: TSQLProducts);
  published
    property UnitPrice: double read FUnitPrice write FUnitPrice;
    property Quantity: integer read FQuantity write FQuantity;
    property Discount: double read FDiscount write FDiscount;
    property OrderID: TSQLOrders read GetOrderID write SetOrderID;
    property ProductID: TSQLProducts read GetProductID write SetProductID;
  end;

implementation

{ TSQLOrderDetails}
function TSQLOrderDetails.GetOrderID: TSQLOrders;
begin
  result := FOrderID.Value;
end;

procedure TSQLOrderDetails.SetOrderID(const Value: TSQLOrders);
begin
  FOrderID.Value := Value;
end;

function TSQLOrderDetails.GetProductID: TSQLProducts;
begin
  result := FProductID.Value;
end;

procedure TSQLOrderDetails.SetProductID(const Value: TSQLProducts);
begin
  FProductID.Value := Value;
end;

Besides mapping tables to classes and table columns to fields/properties, mORMot could also maps relationships (foreign keys) to object associations.

ab · 2013-04-20 07:15:39

1. Please read the SAD pdf 1.18 documentation about master/detail, one to many and many to many relationship.

At ORM level, you define published properties with the proper refered TSQLRecord, as such:

TSQLOrderDetail = class(TSQLRecord)
...
published
    property UnitPrice: double read FUnitPrice write FUnitPrice;
    property Quantity: integer read FQuantity write FQuantity;
    property Discount: double read FDiscount write FDiscount;
    property Order: TSQLOrder read FOrder write TOrder;
    property Product: TSQLProduct read FProduct write FProduct;
  end;

You have methods to work as expected at TSQLRest level.

Two items in the roadmap:
- Allow easier joined query, with no direct query;
- Create Foreign Keys automatically.

Note that foreign key is an integrity feature at database level.
There is already integrity feature at the ORM level, in the framework!

2. For such master/detail, you should better not use a RDBMS classic approach, but data sharding, with a dynamic array.
See the SAD pdf for info about this implementation pattern, which fits better than an Master/Detail to an ORM.

Forget about your RDBMS background!
Think objects!
Do not try to have your objects map your database, but the DB store your objects!

warleyalex · 2013-04-20 20:47:24

ab wrote:

2. For such master/detail, you should better not use a RDBMS classic approach, but data sharding, with a dynamic array.
See the SAD pdf for info about this implementation pattern, which fits better than an Master/Detail to an ORM.
Forget about your RDBMS background!
Think objects!
Do not try to have your objects map your database, but the DB store your objects!

Thanks for replying.

The problem is I just don't know how to implement this approach.
-------
I had to use boils down (to do a MacGyver) to handle master detail and subgrid with mORMot with jqGrid.

Finally the grid automatically postback to the server when end-users select a row and will fire the RowSelecting event handler and databind a second grid with details. Subgrid is also working fine.

That's so beautiful.

Click here to see my 3.5-minute video
http://youtu.be/CB8p8eW4pvM

ab · 2013-04-21 07:24:53

Nice!

Some remarks:

0. Do you connect to MS SQL directly via an external table? (I think not) Did you notice that our SynDBExplorer tool is able to export any supported DB into SQLite3?
1. If you do not use the ORM to create the database, I suspect it is not mandatory to define the field length (via attributes like 'index 50');
2. I still do not understand why foreign keys are mandatory in such cases - IMHO this is an added value about data integrity, but not a requirement;
3. If I understand well, you are changing the JSON format on the client side - this is the best implementation possible, I agree with you;
4. All the existing DB mapping is an item on the roadmap.

Thanks for sharing!

mORMot Open Source

#1 2013-04-11 20:44:22

jqGrid with mORMot

#2 2013-04-12 09:46:49

Re: jqGrid with mORMot

#3 2013-04-19 20:11:30

Re: jqGrid with mORMot

#4 2013-04-20 07:15:39

Re: jqGrid with mORMot

#5 2013-04-20 20:47:24

Re: jqGrid with mORMot

#6 2013-04-21 07:24:53

Re: jqGrid with mORMot

Board footer