# Record Field Eager Loading Optimization

## Overview
Implemented eager loading for record reference fields to eliminate N+1 query problems when displaying tables with record-type fields.

## Problem
Previously, when displaying a table with record-type fields (fields with `content_type = 'record'`), the `x-table.record-name` component would make a separate Livewire call to `getfieldname()` for each record in each row. This created an N+1 query problem that significantly impacted performance.

## Solution
Modified the query builder to eagerly load all record reference data using LEFT JOINs, so the titles and IDs are available in the initial query result without requiring additional lookups.

## Changes Made

### 1. Index.php - New Helper Method
Created `applyRecordFieldJoins()` method that:
- Identifies all fields where `content_type === 'record'`
- For each record field, performs a LEFT JOIN to the referenced table
- Uses the `refid` column for joins (as all IDs point to refid, not id)
- Selects the title and id from referenced tables with aliases:
  - `{fieldname}_title` - The title of the referenced record
  - `{fieldname}_id` - The internal ID for building hrefs

### 2. Index.php - Applied to All Query Paths
The helper method is now used in:
- **Main query**: Regular record lists
- **Sublist query (relationship-based)**: When using BelongsToMany or other relationships
- **Sublist query (foreign key-based)**: When using direct foreign key relationships

Special handling for BelongsToMany relationships to include pivot table columns.

### 3. tablefields.blade.php - Pass Preloaded Data
Updated the record field case to:
- Extract preloaded title using `{fieldname}_title` column
- Extract preloaded id using `{fieldname}_id` column  
- Get the referenced record type's scriptid for route generation
- Pass all preloaded data to the record-name component

### 4. record-name.blade.php - Use Preloaded Data
Updated the component to:
- Accept new props: `preloadedTitle`, `preloadedId`, `recordTypeScriptId`
- Initialize Alpine.js data with preloaded values when available
- Build the href route using the preloaded data
- Only fall back to async `getfieldname()` call when preloaded data is not available
- This maintains backward compatibility for cases where the component is used without preloaded data

## Performance Impact
- **Before**: N queries where N = number of records × number of record-type fields
- **After**: All record reference data loaded in the initial query
- **Example**: A table with 50 records and 3 record-type fields:
  - Before: 1 (main query) + 150 (record field lookups) = 151 queries
  - After: 1 query with 3 LEFT JOINs = 1 query

## Technical Details

### Join Strategy
- Uses LEFT JOIN to handle cases where referenced records may not exist
- Joins on `main_table.fieldname = ref_table.refid` (not id)
- Each join gets a unique alias: `ref_{fieldname}`
- **Only selects minimal required columns** from joined tables:
  - `id` - needed to build the href route
  - `title` - needed for display
  - Note: `refid` is NOT selected from joined tables (already available in main table)

### Important: Column Qualification
When adding LEFT JOINs to a query, column names in WHERE clauses must be qualified with their table names to avoid SQL ambiguity errors. This is especially important for columns that may exist in multiple tables:

**Issue**: 
```sql
-- This will fail with "Column 'isinactive' is ambiguous" if joined tables also have 'isinactive'
WHERE isinactive = 0
```

**Solution**:
```sql
-- Always qualify with table name
WHERE main_table.isinactive = 0
```

All WHERE clauses, filters, and conditions now properly qualify column names with `$mainTable.` prefix to prevent ambiguity when record reference tables are joined.

### Data Flow
1. Query builder LEFT JOINs all record reference tables
2. Selects additional columns: `ref_{fieldname}.title as {fieldname}_title`
3. Records returned with title data already attached
4. Blade template extracts preloaded data and passes to component
5. Alpine.js component uses preloaded data immediately, no async call needed

### Backward Compatibility
The solution maintains backward compatibility:
- The `getfieldname()` method is still available and functional
- Components without preloaded data will fall back to async loading
- No breaking changes to existing APIs or component interfaces

## Files Modified
1. `/src/App/Livewire/Records/Index.php`
   - Added `applyRecordFieldJoins()` helper method
   - Updated main query to use eager loading
   - Updated both sublist query sections to use eager loading
   - **Fixed ambiguous column references**: Updated all WHERE clauses to qualify column names with table names to prevent SQL ambiguity errors when JOINs are present
     - `isinactive` → `$mainTable.isinactive`
     - `netamount` → `$mainTable.netamount`
     - `item` → `$mainTable.item`
     - All filter columns now qualified in `applyFilters()` and `applyValueFilters()` methods

2. `/resources/views/tenant/record/partials/tablefields.blade.php`
   - Updated `@case('record')` to extract and pass preloaded data

3. `/resources/views/components/table/record-name.blade.php`
   - Updated to accept and use preloaded data
   - Falls back to async loading when needed

## Testing Recommendations
1. Test with tables containing multiple record-type fields
2. Verify record links are correct and clickable
3. Test with missing/null record references (should show '--')
4. Test with BelongsToMany relationships (sublists)
5. Monitor query count in debug bar to confirm optimization
6. Test sorting by record-type fields
7. Test filtering on record-type fields

## Future Enhancements
- Consider caching record type lookups to further optimize performance
- Add query result caching for frequently accessed tables
- Consider implementing similar optimization for other field types that require lookups