[Valentina] M:M problems

[Eric Forget]

Hi Ruslan,

> Just to clarify your mistakes.
> 
> Eric, your above SQL query touch 3(!!!) tables and do 2 joins.
> In fact you can do the same touching only one table.
> 
> Next, IF you was going find if pair exists, you do this wrong.
> You need build query as:
> 
>   SELECT * FROM GROUPPerson
>   WHERE GroupPtr = groupIndex
>           AND PersonPtr = personIndex
> 
> To do this you need use sprintf()
> 
>   q = "SELECT * FROM GROUPPerson WHERE GroupPtr = %d AND PersonPtr = $d"
>   sprintf( buff, q, groupIndex, personIndex );
> 
> Now you have good SQL query string which can be send to database.

Thanks now it works!
 
>> Also, how do you get an FBL_ArraySet with all the person of a certain group?
> 
> Well, Eric, note that you can work only with bitSets and arraySets to do
> searches manually, using Find() function of VDK_Field.
> Or you can do searches using SQL and get as result VDK_Cursor.
> 
> First way is MUCH FASTER, but require more work from you in coding.
> This is like old known navigation access to records. Although one little
> difference you work not with single current record, but with sets of
> records.
> 
> In the same time, first way, allow you query only single table,
> So if you need some kind of complex query, and you want work with complex
> relational model, you need choose SQL.
> 
> Of course in sample places you can use BitSets to optimize simple searches.
> 
> --------
> So, how to get ArraySet of all person of specific group:
> Very simple. You need touch only one table
> 
> FBL_ArraySet* FindPersonsOfGroup( FBL_REC_ID inGroupRecID )
> {
>   BitSet* s1 = mGroupPerson.mGroupPtr.Find( &inGroupRecID );
> 
>   FBL_AraySet* res = new FBL_ArraySet( s1->getCount() );
>   
>   // now you have set of records for this group,
>   // and you need just iterate it, and extract RecIDs of Persons.
> 
>   BitSetIterator it( *s1 );
>   FBL_REC_ID gp = it.First(); // this is RecID of GroupPerson table.
>   while( gp )
>   {
>       if( mGroupPerson.GoTo( gp ) )
>       {
>           res->Add( mGroupPerson.mPersonPtr );
>       }
> 
>       gp = it.Next();
>   }
> 
>   return res;
> }

I'm just wondering, if I have a very big table, it will be fast anyway? How
about the memory occupied?

Also, I need the array to be sorted. How I should do it?

Finally, if I create an FBL_AraySet from a FBL_BitSet like this:

mGroupArray = mReceipe.Sort(allSet, mGroup.mName, true);

It looks like I have to keep alive allSet until mGroupArray is not deleted.
Otherwise, mGroupArray becomes corrupted, isn't it?
 
> To get the same functionality in SQL:
> 
> void FindPersonsOfGroup( FBL_REC_ID inGroupRecID )
> {
>   char q = "SELECT Person FROM GroupPerson WHERE Group = %d";
>   char query[256];
> 
>   sprintf( query, q, inGroupRecID );
> 
>   VDK_Cursor* curs = SqlSelect( query );
> 
>   // Now you have cursor with ONE field that in fact contains
>   // RecIDs of persons you need. Just iterate it.
>   
>   ....
> }

I don't care using SQL, how slower is it?

Thanks,

Éric 

___________________________________________________________________

 Eric Forget                       Cafederic
 ForgetE at cafederic.com             <http://www.cafederic.com/>

 Fingerprint <86D5 38F5 E1FD 5D9C 71C3  BAA3 797E 70A4 6210 C684>