[FX.php List] Portal vs 2nd query [Was: Related Records]

[Derrick Fogle]

If you can display it in a portal in FM, you can get that exact same  
data into FX.php. So it depends on the FM relationships. I have at  
least one instance where I display almost exactly what you describe  
below

On Mar 20, 2006, at 4:15 PM, Joel Shapiro wrote:

> Thanks very much Andy, that all makes good sense.
>
> Question for you on your example, though.  You state at the end of  
> your post the possibility of displaying related contact- 
> phonenumbers.  If I understand correctly, this would be a  
> 'grandchild' relationship: Business->Contact->ContactPhone.  How  
> would this be possible using only portals -- and not additional  
> queries?  (i.e. wouldn't you need to have a portal within a  
> portal?  [& not using GetNthRecord calcs])
>
> Thanks,
> -Joel
>
>
> On Mar 20, 2006, at 1:51 PM, Andy Gaunt wrote:
>
>> Joel,
>>
>> I personally find portals on the layout to be faster. The reason  
>> being, it
>> is a single query to FileMaker.
>>
>> It can also depend on what you are doing. Say your first query is for
>> companies you have done business with in the last 30 days. This  
>> returns 50
>> records.
>>
>> Now, you are looping through those company records with your  
>> FX.PHP to find
>> the Contacts at that company. So, you do a second FX.PHP query.
>>
>> This then returns 5 records for company 1, 10 for company 2, 3 for  
>> company 3
>> and so on.
>>
>> The issue here is that you are doing your second FX query within a  
>> foreach
>> loop of the initial query. So instead of 1 query to the database  
>> we now have
>> 51 queries.
>>
>> 1 for the initial search and then 50 for the related contacts.
>>
>> Imagine if you also wanted to display contact numbers for those  
>> contacts
>> that is stored in another table. Add this to the mix and you start  
>> to see
>> the problem.
>>
>> If you use portals, you can limit your number of queries and use  
>> simple for
>> and while loops to process this data. Plus, you will only loop  
>> through the
>> FX.PHP data array once this way, as opposed to multiple times.
>>
>> My 2cents anyways.
>>
>>
>> Andy Gaunt
>> T: 407.810.4722
>> andy at fmpug.com
>> http://www.fmpug.com
>>
>> Recipient of FileMaker's 2005 "Mad Dog" Public Relations Award
>>
>> For chapter locations, dates & times please visit the website at
>> http://www.fmpug.com If you can make it to a meeting, please RSVP at
>> http://www.fmpug.com/rsvp.php
>>
>>
>> -----Original Message-----
>> From: fx.php_list-bounces at mail.iviking.org
>> [mailto:fx.php_list-bounces at mail.iviking.org] On Behalf Of Joel  
>> Shapiro
>> Sent: Monday, March 20, 2006 3:36 PM
>> To: FX.php Discussion List
>> Subject: [FX.php List] Portal vs 2nd query [Was: Related Records]
>>
>> Hi all
>>
>> I've often wondered which produces better performance (or is better
>> design):
>>
>> a) Having a relationship in FileMaker and viewing related records
>> through the portal (and code similar to Derrick's below),
>>    or
>> b) Keeping the FM tables as 'raw data' and querying the related
>> records through PHP after first querying the 'parent' record.
>>
>> I have one page in a project with multiple 'nested' queries, since
>> you can't have portals within portals, but when it's just a matter of
>> multiple children to one parent, how does the 2nd query balance
>> against the FM relationship - performance/stability-wise?
>>
>> Any thoughts or words of wisdom?
>>
>> TIA,
>> -Joel
>>
>>
>> Joel Shapiro - FileMaker Pro Database Design
>> ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
>> joelshapiro at jsfmp dot com
>> http://www.jsfmp.com
>> 415-269-5055
>>
>>
>> On Mar 20, 2006, at 8:28 AM, Derrick Fogle wrote:
>>
>>> If you want it to be similar to an FM portal, then use the portal.
>>> The last array element in any FX field reference indicates it's
>>> repeating field number or portal row number. You only need a single
>>> database query, and will get all the data on the referenced layout.
>>> Here's a sample of the code I use:
>>>
>>> $prow = 0;
>>> while($value['Relationshipname::ID_Field'][$prow]) {
>>> 	echo $value['Relationshipname::Data_Field'][$prow];
>>> 	echo "<br />";
>>> 	$prow++;
>>> }
>>>
>>> Salt to taste...
>> _______________________________________________
>> FX.php_List mailing list
>> FX.php_List at mail.iviking.org
>> http://www.iviking.org/mailman/listinfo/fx.php_list
>>
>>
>> _______________________________________________
>> FX.php_List mailing list
>> FX.php_List at mail.iviking.org
>> http://www.iviking.org/mailman/listinfo/fx.php_list
>
> _______________________________________________
> FX.php_List mailing list
> FX.php_List at mail.iviking.org
> http://www.iviking.org/mailman/listinfo/fx.php_list


Derrick Fogle
derrick at fogles.net