[Valentina] M:M problems

[Ruslan Zasukhin]

on 1/20/03 4:47 AM, Eric Forget at forgete at cafederic.com wrote:

> And now I'm trying to add a person to a group:
> 
> void
> CDatabase::AddPersonToGroup(UInt32 personIndex, UInt32 groupIndex)
> {
>   if (GotoPerson(personIndex) and GotoGroup(groupIndex)) {
>   
>       char        query[] = "SELECT * FROM GroupPerson WHERE Group.RecID =
> GroupPtr and Person.RecID = PersonPtr";
>       VDK_Cursor  cursor(this);
>       int         count;
>       
>       
>       cursor.ExecuteSQL(query);
>       
>       count = cursor.GetRecordCount();
>       if (count == 0) {
>       
>           // Add a new record in the mGroupPerson table...
>       }
>   }
> }
> 
> The count variable always return 0.

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.


> 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;
}


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.
    
    ....
}




-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://listserv.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------