please help!

noobies · 2011-09-13 12:10:48

Table1
id data
----------
1 | One
2 | Two
3 | Three
4 | Four

Table2
id id_t1 otherdata
-------------------------
1 | 1 | otherdata
2 | 1 | otherdata
3 | 2 | otherdata
4 | 2 | otherdata

need run query: select Table1.data, Table2.otherdata from Table2 inner join Table1 on Table2.id_t1 = Table1.id to get

ResultTable
id data otherdata
-------------------------
1 | One | otherdata
2 | One | otherdata
3 | Two | otherdata
4 | Two | otherdata

how get resulttable in SQLite3 Framework?

Leander007 · 2011-09-13 12:20:30

It is very simple if you use TSQLTable directly e.g.:

  (...)
  sql := StringToUTF8('select * from T1 left join T2 on ...');
  table := SQLite3.TSQLTableDB.Create(rest,[TSQLT1, TSQLT2], sql, false);
(...)

Last edited by Leander007 (2011-09-13 12:20:57)

noobies · 2011-09-23 06:11:19

maybe any create very simple sample using this method?

noobies · 2011-09-23 11:43:47

I solved problem using for TSQLTable, but i dont understand how use TSQLTableDB

var
 TableRole: TSQLTable;
 sql: string;
begin
  sql := 'select UserRoles.ID, UserRole.RoleName from UserRoles inner join UserRole on UserRoles.Dest=UserRole.ID';
  TableRole := Database.ExecuteList([], sql);
  TSQLTableToGrid.Create(dgRoles, TableRole, Database);
end;

Last edited by noobies (2011-09-23 11:44:09)

Leander007 · 2011-09-23 12:28:52

I already showed you with previous post.

What you are naming Database is named in my case as rest.

So lets repeat and complete with your variables :

var
 TableRole: TSQLTable;
 sql: string;
begin
  sql := StringToUTF8('your join sql here');
  TableRole := TSQLTableDB.Create(Database,[TSQLUserRoles, TSQLUserRole], sql, false);
  TSQLTableToGrid.Create(dgRoles, TableRole, Database);
end;

That is all. You must first read the SAD documentation and take MainDemo as first reference and Arnaud code (and comments) as MASTER REFERENCE !

noobies · 2011-09-23 13:09:24

i try execute you sample, but i get error: [DCC Error] uMain.pas(347): E2010 Incompatible types: 'TSQLDatabase' and 'TSQLRestClientURI'

var
  Database: TSQLRestClientURI;

ab · 2011-09-23 13:29:38

The TSQLRestClientURI is definitively not a TSQLDatabase type.
You are mixing Client/Server and low-level SQLite3 types, which can not be done.

TSQLRestClientURI is to be used on the client side, where TSQLDatabase is not available, since by definition there is no DB on the client side.

TSQLDatabase or TSQLTableDB may be used on server side only - and even there, IMHO this may not be a good idea, because you'll loose the MVC architecture profit and most of the ORM benefits.

Normally, you should not have to use directly TSQLDatabase in your application, if you use the ORM. The ORM will call the database engines for you.

For your purpose, you may use all TSQLRestClientURI methods for retrieving lists.
You should not use all low-level SynSQLite3/SQLite3 units in your programs, when using the ORM.
Just high-level SQlite3Commons-based classes and methods, which will make all the work for you.

I suggest you take some time to read the documentation (in its latest revision, which is more complete and contains some more architecture diagrams which may help you guess how it works), to better understand how mORMot works.

noobies · 2011-10-25 06:15:36

my application is required to bind the table often

works, but due to lack of communication between the tables is obtained by nonsense

table := Database.List([TSQLInsertion, TSQLProduct], 'articul, insertion.name');

works, but it looks awkward

table := Database.ExecuteList([TSQLInserts, TSQLProduct], 'select p.articul, t.name from product p inner join (inserts ii inner join insertion i on ii.dest = i.id) t on p.id = t.source');

is it possible to create a table without using SQL directly?
something like this: http://www.sql.ru/forum/actualthread.aspx?tid=817359

Last edited by noobies (2011-10-25 06:17:25)

ab · 2011-10-25 12:19:57

What is wrong with the current many to many implementation ?
See TSQLRecordMany and the associated pages in the documentation - http://synopse.info/fossil/wiki?name=Downloads
I think you have already all needed methods at hand.
You can't just create your own join between the records, but you'll be able to do it using TSQLRecordMany kind of tables.

Or consider using a dynamic array, to allow a "sharding" architecture - see http://blog.synopse.info/post/2011/07/0 … chitecture

noobies · 2011-10-25 13:45:32

I read the documentation, but I did not get to realize the following example

TSQLProduct = class;

TSQLSize = class(TSQLRecord)
private
  fName: UTF8String;
published
  property Name: UTF8String read fName write fName;
end;

TSQLSizes = class(TSQLRecordMany)
private
  fSource: TSQLProduct;
  fDest: TSQLSize;
published
  property Source: TSQLProduct read fSource write fSource;
  property Dest: TSQLSize read fDest write fDest;
end;

TSQLForm = class(TSQLRecord)
private
  fName: UTF8String;
published
  property Name: UTF8String read fName write fName;
end;

TSQLForms = class(TSQLRecordMany)
private
  fSource: TSQLProduct;
  fDest: TSQLForm;
published
  property Source: TSQLProduct read fSource write fSource;
  property Dest: TSQLForm read fDest write fDest;
end;

TSQLCategory = class(TSQLRecord)
private
  fName: UTF8String;
published
  property Name: UTF8String read fName write fName;
end;

TSQLCategories = class(TSQLRecordMany)
private
  fSource: TSQLProduct;
  fDest: TSQLCategory;
published
  property Source: TSQLProduct read fSource write fSource;
  property Dest: TSQLCategory read fDest write fDest;
end;

TSQLProduct = class(TSQLRecord)
private
  fName: UTF8String;
  fOwner: UTF8String;

  fCategories: TSQLCategories;
published
  property Name: UTF8String read fName write fName;
  property Owner: UTF8String read fName write fName;

  property Categories: TSQLCategories read fCategories write fCategories;
end;

sample database:

Category
id | name
1 | boy
2 | girl

Size
id | name
1 | small
2 | medium
3 | big

Categories
id | source | dest
1 | 1 | 1
2 | 1 | 2
3 | 2 | 2
4 | 3 | 1
5 | 4 | 1
6 | 5 | 2
7 | 6 | 2

Sizes
id | source | dest
1 | 1 | 1
2 | 2 | 1
3 | 2 | 2
4 | 3 | 1
5 | 3 | 2
6 | 3 | 3
7 | 4 | 2
8 | 5 | 2
9 | 5 | 3
10 | 6 | 1
11 | 6 | 3

Forms
id | source | dest
1 | 1 | 1
2 | 1 | 3
3 | 1 | 4
4 | 2 | 3
5 | 3 | 2
6 | 3 | 3
7 | 4 | 3
8 | 4 | 4
9 | 5 | 1
10 | 6 | 1
11 | 6 | 4

need select all products with owner mark, category for boy, size - small or medium, form - circle or square.
I know how to do it directly using Sql, but I do not understand how to make it through the ORM

can you implement this example, it will be very helpful for beginners

Last edited by noobies (2011-10-25 13:52:38)

noobies · 2011-11-07 07:51:10

i read documentation but i don't understand.
i create sample, left 3 button work great, but it sql direct command. In right button i try create not sql direct, first button work, but other button not work, please show me how build this selection (on button title) in orm and rest style

http://dl.dropbox.com/u/12662204/sample.7z

ab · 2011-11-07 10:30:22

About TForm1.Button4Click, you use product.FillTable so if you release product, the FillTable will be freed.
You can set FillTable.OwnerMustFree := false to let the table be used and safely call product.Free;

TSQLRecordMany only handle directly one join level (as with Button4Click).
So for such cases, using SQL does make sense.

To be pure ORM, we would need a new method to make it simple.
I'll look into finding a easier syntax, for instance able to retrieve directly TSQLProduct instances with a Categories/Sizes/Forms properties filled with the corresponding rows. And an easy to write associated "cross-joined" where clause.

Something like that:

aProd: TSQLProduct;

aProd := TSQLProduct.CreateAndFillPrepareMany(Database,
  'Owner=? and Categories.Dest.Name=? and (Sizes.Dest.Name=? or Sizes.Dest.Name=?)',[],
  ['mark','for boy','small','medium']);
// this will create the JOIN as such:
// where p.owner="mark" and c.name = "for boy" and (s.name = "small" or s.name = "medium")
// and will create Categories and Sizes instances to be used directly from the loop
if aProd<>nil then
try
  while aProd.FillOne do
     // here e.g. aProd.Categories.Dest are instantied (and Categories.Source=aProd)
     writeln(aProd.Name,' ',aProd.Owner,' ',aProd.Categories.Dest.Name,' ',aProd.Sizes.Dest.Name);
  // you may also use aProd.FillTable to fill a grid, e.g.
finally
  aProd.Free; // will also free aProd.Categories/Sizes instances
end;

It is some sensitive addition to the framework, but I suspect it is worth it.
The TSQLRecordMany.FillMany() methods is limited to one level of join, whereas such a new method may extend it to any JOIN level.

What do you think about that?

noobies · 2011-11-07 12:07:20

i think is very good idea create new method.
and one small question:
assume that more than two categories and I need to choose two categories, I do not know how to do it
sql direct: i simple add new select parameters

and c.name = "for boy"

to

and (c.name = "for boy" or c.name = "for girl")

but i don't understand how change ORM code to work with many parameters. Method SourceGet give only one ID parameter;

var
  table: TSQLTable;
  product: TSQLProduct;
  category: TSQLCategory;
  fIds: TIntegerDynArray;
  s: string;
begin
  product := TSQLProduct.Create;
  category := TSQLCategory.Create(Database, 'name = "for boy"');
  product.Categories.SourceGet(Database, category.ID, fIds);
  try
    s := IntegerDynArrayToCSV(fIds, length(fIds), 'id in (', ')');
    product.CreateAndFillPrepare(Database, s + 'and owner = "mark"');
    table := product.FillTable;
    table.OwnerMustFree := False;
  finally
    FreeAndNil(product);
    FreeAndNil(category);
  end;
  TSQLTableToGrid.Create(DrawGrid1, table, Database);

Last edited by noobies (2011-11-07 12:09:03)

ab · 2011-11-07 12:28:17

The ORM code does allow only access to the Source and Dest tables, i.e. only one level of join.
In its current implementation, you can't use TSQLRecordMany methods using a join over more than one table.

That is the reason why I propose to add a new method.
The current version will only handle published Source and Dest fields.

ab · 2011-12-07 08:22:15

That'is it, I've implemented this feature!
http://blog.synopse.info/post/2011/12/0 … JOIN-query

The standard ORM methods ManySelect, DestGetJoined... are used to retrieve the relations between tables from the pivot table point of view. This saves bandwidth, and can be used in most simple cases, but it is not the only way to perform requests on many-to-many relationships. And you may have several TSQLRecordMany instances in the same main record - in this case, those methods won't help you.

It is very common, in the SQL world, to create a JOINed request at the main "Source" table level, and combine records from two or more tables in a database. It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two or more tables by using values common to each. Writing such JOINed statements is not so easy by hand, especially because you'll have to work with several tables, and have to specify the exact fields to be retrieved; if you have several pivot tables, it may start to be a nightmare. Let's see how our ORM will handle it.

A dedicated FillPrepareMany method has been added to the TSQLRecord class, in conjunction with a new constructor named CreateAndFillPrepareMany. This particular method will:
- Instantiate all Dest properties of each TSQLRecordMany instances - so that the JOINed request will be able to populate directly those values;
- Create the appropriate SELECT statement, with an optional WHERE clause.

Here is the test included in our regression suite, working with the same database:

Check(MS.FillPrepareMany(aClient,
'DestList.Dest.SignatureTime<>% and id>=? and DestList.AssociationTime<>0 '+
'and SignatureTime=DestList.Dest.SignatureTime '+
'and DestList.Dest.Signature<>"DestList.AssociationTime"',[0],[sID[1]]));

Of course, the only useful parameter here is id>=? which is used to retrieve the just added relationships in the pivot table. All other conditions will always be true, but it will help testing the generated SQL.

Our mORMot will generate the following SQL statement:

select A.ID AID,A.SignatureTime A00,A.Signature A01,
B.ID BID,B.AssociationTime B02,
C.ID CID,C.SignatureTime C00,C.Signature C01
from ASource A,ADests B,ADest C
where B.Source=A.ID and B.Dest=C.ID
and (C.SignatureTime<>0 and A.id>=:(1): and B.AssociationTime<>0
and A.SignatureTime=C.SignatureTime and C.Signature<>"DestList.AssociationTime")

You can notice the following:
- All declared TSQLRecordMany instances (renamed B in our case) are included in the statement, with all corresponding Dest instances (renamed as C);
- Fields are aliased with short unique identifiers (AID, A01, BID, B02...), for all simple properties of every classes;
- The JOIN clause is created (B.Source=A.ID and B.Dest=C.ID);
- Our manual WHERE clause has been translated into proper SQL, including the table internal aliases (A,B,C) - in fact, DestList.Dest has been replaced by C, the main ID property has been declared properly as A.ID, and the "DestList.AssociationTime" text remained untouched, because it was bounded with quotes.

That is, our ORM did make all the dirty work for you! You can use Delphi-level conditions in your query, and the engine will transparently convert them into a valid SQL statement. Benefit of this will become clear in case of multiple pivot tables, which are likely to occur in real-world applications.

After the statement has been prepared, you can use the standard FillOne method to loop through all returned rows of data, and access to the JOINed columns within the Delphi objects instances:

Check(MS.FillTable.RowCount=length(sID));
for i := 1 to high(sID) do begin
MS.FillOne;
Check(MS.fID=sID[ i]);
Check(MS.SignatureTime=MD.fSignatureTime);
Check(MS.DestList.AssociationTime=i);
Check(MS.DestList.Dest.fID=dID[ i]);
Check(MS.DestList.Dest.SignatureTime=MD.fSignatureTime);
Check(MS.DestList.Dest.Signature=FormatUTF8('% %',[aClient.ClassName,i]));
end;
MS.FillClose;

Note that in our case, an explicit call to FillClose has been added in order to release all Dest instances created in FillPrepareMany. This call is not mandatory is you call MS.Free directly, but it is required if the same MS instance is about to use some regular many-to-many methods, like MS.DestList.ManySelect() - it will prevent any GPF exception to occur with code expecting the Dest property not to be an instance, but a pointer(DestID) value.

This will enable direct code like the one stated above - http://synopse.info/forum/viewtopic.php?pid=2940#p2940

mORMot Open Source

#1 2011-09-13 12:10:48

please help!

#2 2011-09-13 12:20:30

Re: please help!

#3 2011-09-23 06:11:19

Re: please help!

#4 2011-09-23 11:43:47

Re: please help!

#5 2011-09-23 12:28:52

Re: please help!

#6 2011-09-23 13:09:24

Re: please help!

#7 2011-09-23 13:29:38

Re: please help!

#8 2011-10-25 06:15:36

Re: please help!

#9 2011-10-25 12:19:57

Re: please help!

#10 2011-10-25 13:45:32

Re: please help!

#11 2011-11-07 07:51:10

Re: please help!

#12 2011-11-07 10:30:22

Re: please help!

#13 2011-11-07 12:07:20

Re: please help!

#14 2011-11-07 12:28:17

Re: please help!

#15 2011-12-07 08:22:15

Re: please help!

Board footer