Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: mongoDB $lookup function - simple edit form


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 10 - Last Post: Feb 8, 2018 11:09 PM Last Post By: Dmitry Arefiev
Werner Selen

Posts: 22
Registered: 1/19/09
mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 9, 2015 9:01 AM
Hi,

Investigating mongoDB with great interest and having a couple questions:

Find out there is a $lookup feature to simulate a sql join available with the MongoDB version 3.2.
How can we use this $lookup using FireDac. Is there somewhere a lookup property ?

Using a simple edit form with, for example, 2 TDBEdit components linked to fields,
5 buttons like New, Update, Delete, Save, Cancel for crud operations.
How to interact with the dataset, can we use dataSet.insert, dataset.edit, dataset.save, dataset.cancel ?
Do we need to use FDMongeQuery1.Cachedupdates:=True when updating, inserting, saving, deleting one record on a edit form?

Thanks for any info

Edited by: Werner Selen on Dec 9, 2015 9:01 AM

Edited by: Werner Selen on Dec 9, 2015 9:01 AM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 9, 2015 9:21 PM   in response to: Werner Selen in response to: Werner Selen
How can we use this $lookup using FireDac. Is there somewhere a lookup property ?

DX10 does not support $lookup stage. But it would be not complex to
subsclass TMongoPipeline and and introduce there Lookup method.
It will be similar, for example, to TMongoPipeline.Redact method.

How to interact with the dataset, can we use dataSet.insert, dataset.edit, dataset.save, dataset.cancel ?

Yes, you can. See Object Pascal\Database\FireDAC\Samples\DBMS Specific\MongoDB\DataSet demo.
Note, if your collection is empty, then you should first populate dataset FieldDefs.

Do we need to use FDMongeQuery1.Cachedupdates:=True when updating, inserting, saving, deleting one record on a edit form?

Rather no need.

--
With best regards,
Dmitry
Werner Selen

Posts: 22
Registered: 1/19/09
Re: mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 10, 2015 7:49 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry this works very good !, few more questions:

Is there a way to insert/update the array (detail data) using the dataset too?
I am able to insert the master data, but not the detail, please look at the questions below in the code

procedure TfrmMain.btnInsertdataClick(Sender: TObject);
begin
   //------- using dataset -----------------------------
 
   // insert new record = works
   dsRestaurants.DataSet.Insert;
   dsRestaurants.DataSet.FieldByName('Name').AsString:='TEST3';
   dsRestaurants.DataSet.FieldByName('Address.zipcode').AsString:='10075';
 
   // insert new coords array = works - but is this the right way?
   dsCoords.DataSet.Insert;
   dsCoords.DataSet.Fields[0].AsFloat:=73.9557413;
   dsCoords.DataSet.Insert;
   dsCoords.DataSet.Fields[0].AsFloat:=40.7720266;
 
   // insert new grades
   dsGrades.DataSet.Insert;
   //how to insert array grades ??
 
   //------- using FDMongoQuery -----------------------------
 
   // insert new record = works
   FDMongoQuery1.Insert;
   FDMongoQuery1.FieldByName('Name').AsString:='TEST4';
   FDMongoQuery1.FieldByName('Address.zipcode').AsString:='10075';
 
   // insert new coords array
   //FDMongoQuery1.FieldByName('coord').   // how to insert coord array?
 
    // insert new grades
   //FDMongoQuery1.FieldByName('grades').   // how to insert grades array?
end;


=======================================

To find data, is it save to use the dataset too ?
But how to find the array data this way?

procedure TfrmMain.btnFindDataClick(Sender: TObject);
begin
   // find record = works
   if dsRestaurants.DataSet.Locate('name','Vella',[]) then begin
      ShowMessage('found');
      dsRestaurants.DataSet.Edit;
      dsRestaurants.DataSet.FieldByName('name').AsString:='Vella updated';
      dsRestaurants.DataSet.Post;
   end;
 
   ShowMessage('via findkey');
 
   // find record = works
   if FDMongoQuery1.FindKey(['Vella']) then begin
      ShowMessage('found');
      FDMongoQuery1.Edit;
      FDMongoQuery1.FieldByName('name').AsString:='Vella updated2';
      FDMongoQuery1.Post;
   end;
end;
 
   // how to find a array record?
 


Thank you
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 15, 2015 10:03 PM   in response to: Werner Selen in response to: Werner Selen
// insert new coords array = works - but is this the right way?
dsCoords.DataSet.Insert;
dsCoords.DataSet.Fields[0].AsFloat:=73.9557413;

Yes, this is the right way.

// insert new grades
dsGrades.DataSet.Insert;
//how to insert array grades ??

dsGrades.DataSet.Insert;
oAdt := (dsGrades.DataSet.Fields[0] as TADTField);
oAdt.Fields[0].AsDateTime := Now;
oAdt.Fields[1].AsString := 'B';
oAdt.Fields[2].AsInteger := 18;


// insert new coords array
//FDMongoQuery1.FieldByName('coord'). // how to insert coord array?

oCoordDS := (FDMongoQuery1.FieldByName('address.coord') as TDataSetField).NestedDataSet;
oCoordDS.Insert;
....


To find data, is it save to use the dataset too ?

Yes, it is.

But how to find the array data this way?

At moment Locate, FindKey, etc search methods do not work with ftArray, ftAdt, ftDataSet
data types. So, you will need to use:

DS.First; 
while not DS.Eof do begin
  <check record>
  DS.Next;
end;

--
With best regards,
Dmitry
Werner Selen

Posts: 22
Registered: 1/19/09
Re: mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 11, 2015 10:58 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
2 more question:

1. FDMongoDataset

Looked at coderange-x video, thought that its said that FDMongoQuery is like FDQuery and FDMongoDataSet is like FDTable
so I wanted to test with the FDMongoDataSet and get the error: ..Mongo-2110.Cannot open dataset [FDMongoDataSet1]. Cursor must be assigned.

looking at example Mongo_explorer I need to attach a cursor

FDMongoDataSet1.Close;
FDMongoDataSet1.Cursor := FMongoConn.ListDatabases;
FDMongoDataSet1.Open;

but how can I use the FDMongoDataSet like the FDMongoQuery, or is this not the purpose for this component and how to use it?


2. Connection Definition Parameters with mongoDB 3.2

Connect to a locally running server:
DriverID=Mongo
Database=test

MongoDB needs to setup a database directory at startup. Can we override this with the connection parameter at startup of the application somehow? MonogDB 3.2 uses now default WiredTiger and there are a lot of files in the data dir now. I can't tell which file is using which database. Is there a way we can name the physical file according our database via settings of the connection parameters?

thanks for any info.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 15, 2015 10:10 PM   in response to: Werner Selen in response to: Werner Selen
Looked at coderange-x video, thought that its said that FDMongoQuery is like FDQuery and FDMongoDataSet is like FDTable
so I wanted to test with the FDMongoDataSet and get the error: ..Mongo-2110.Cannot open dataset [FDMongoDataSet1]. Cursor must be assigned.

FDMongoQuery is like FDQuery - correct.

FDMongoDataSet is not like FDTable. FDMongoDataSet does not have a build-in
MongoDB cursor source. So, the cursor must be assigned by application. Exactly,
as the error message sayd and how Mongo_explorer does.

MongoDB needs to setup a database directory at startup. Can we override this with the connection parameter at startup of the application somehow? MonogDB 3.2 uses now default WiredTiger and there are a lot of files in the data dir now. I can't tell which file is using which database. Is there a way we can name the physical file according our database via settings of the connection parameters?

I have no idea about MongoDB 3.2, because I not yet tested 3.2. But you should
check MongoDB connection string possible options. And if some of the options
will help you, then include it using MongoAdvanced, eg:

MongoAdvanced=option1=value1;option2=value2;...


--
With best regards,
Dmitry
Bob Johnson

Posts: 28
Registered: 9/11/15
Re: mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 27, 2016 8:46 AM   in response to: Werner Selen in response to: Werner Selen
Werner Selen wrote:

looking at example Mongo_explorer I need to attach a cursor

FDMongoDataSet1.Close;
FDMongoDataSet1.Cursor := FMongoConn.ListDatabases;
FDMongoDataSet1.Open;

Ok, so this lists out all databases. But how do I assign a cursor to a specific database and specific collection? I want to work with the records from a collection in DBGrid. Assuming TestDB and TestCollection.

Thanks!
Heronim Marcal

Posts: 1
Registered: 3/3/01
Re: mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 5, 2018 10:35 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:

How to interact with the dataset, can we use dataSet.insert, dataset.edit, dataset.save, dataset.cancel ?

Yes, you can. See Object Pascal\Database\FireDAC\Samples\DBMS Specific\MongoDB\DataSet demo.
Note, if your collection is empty, then you should first populate dataset FieldDefs.

Dear Dmitry,

The above example does not explain how to persist data in MongoDB, since it only changes the data in the DBGrid, not in the db itself.

Can you better elaborate on how the dataset methods would work in this scenario?

Thanks in advance.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 8, 2018 11:09 PM   in response to: Heronim Marcal in response to: Heronim Marcal
You should press 'Save Restaurants' button, to post the changes from dataset to database.
The demo folder contains readme.html with demo description.

--
With best regards,
Dmitry
SQL Daddy

Posts: 6
Registered: 4/8/16
Re: mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Apr 18, 2016 4:06 AM   in response to: Werner Selen in response to: Werner Selen
Hello

There is a possible resourses for mongodb $lookup function .It will helpful for all the queries related to mongo db, for more details you can visit https://zappysys.com/blog/ssis-loading-data-into-mongodb-upsert-update-delete-insert/ .

I hope it will helpful.

Edited by: SQL Daddy on Apr 18, 2016 5:30 AM
P F Lam

Posts: 1
Registered: 8/3/08
Re: mongoDB $lookup function - simple edit form  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 16, 2016 9:21 PM   in response to: SQL Daddy in response to: SQL Daddy
Hi

I am facing a problem to write a mongo syntax “$lookup” with firedac component to left join 2 collections.
can anyone help me with a sample coding on how to write the $lookup method with TMongoPipeline using Delphi 10.1 Berlin?
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02