How can I correctly convert List<SObjectField> into a list of comma-separated field names to build a SOQL String?

Question

I've created a generic selector class as follows:

public inherited sharing class DAO_SObjectSelector {
public List<SObject> selectFor(Query query) {
    return Database.query(query.toString());
}

public class Query {
    SObjectType sObjectType;
    List<SObjectField> queryFieldList;
    List<String> whereFilterList = new List<String>();
    List<SortField> sortFieldList = new List<SortField>();

    public Query(SObjectType sObjectType, List<SObjectField> sObjectFieldList) {
        this.sObjectType = sObjectType;
        this.queryFieldList = sObjectFieldList;
    }

    public Query addWhereFilter(String whereFilter) {
        this.whereFilterList.add(whereFilter);
        return this;
    }

    public Query addSortField(SObjectField sortField, String sortDirection) {
        this.sortFieldList.add(new SortField(sortField, sortDirection));
        return this;
    }

    public override String toString() {
        String queryString = 'SELECT ' + String.join(this.getQueryFieldNameList(), ',');
        queryString += ' FROM ' + this.sObjectType.getDescribe().getName();
        if (!this.whereFilterList.isEmpty()) {
            queryString += ' WHERE ' + String.join(this.whereFilterList, ' AND ');
        }
        if (!this.sortFieldList.isEmpty()) {
            queryString += ' ORDER BY ' + String.join(this.getSortOrderList(), ',');
        }
        System.debug('######### queryString: ' + queryString);
        return queryString;
    }

    private List<String> getQueryFieldNameList() {
        List<String> sObjectFieldNameList = new List<String>();
        for (SObjectField sObjectField : this.queryFieldList) {
            sObjectFieldNameList.add(sObjectField.getDescribe().getName());
        }
        System.debug('######### sObjectFieldNameList: ' + sObjectFieldNameList);
        return sObjectFieldNameList;
    }

    private List<String> getSortOrderList() {
        List<String> sortOrderList = new List<String>();
        for (SortField sortField : this.sortFieldList) {
            sortOrderList.add(sortField.toString());
        }
        return sortOrderList;
    }
}

private class SortField {
    SObjectField sortField;
    String sortDirection;
    public SortField(SObjectField sortField, String sortDirection) {
        this.sortField = sortField;
        this.sortDirection = sortDirection;
    }

    public override String toString() {
        return this.sortField.getDescribe().getName() + ' '
                + ((!String.isBlank(this.sortDirection)) ? this.sortDirection : '');
    }
}
}

I am trying to consume it with the following:

private List<Tower__c> getTowerList() {
    List<SObjectField> sObjectFieldList = new List<SObjectField>{
            Tower__c.Id, Tower__c.Name,
            Tower__c.State__r.Name,
            Tower__c.Tower_Location__Latitude__s, Tower__c.Tower_Location__Longitude__s
    };
    return this.sObjectSelector.selectFor(
            new DAO_SObjectSelector.Query(Tower__c.SObjectType, sObjectFieldList)
                    .addSortField(Tower__c.Name, 'ASC')
    );
}

However, this fails with:

duplicate field selected: Name

because both Tower__c.Name and Tower__c.State__r.Name are being joined to the SOQL query as simply "Name". In other words the query is being rendered as:

SELECT Id,Name,Name,Tower_Location__Latitude__s,Tower_Location__Longitude__s FROM Tower__c ORDER BY Name ASC

How can I modify the DAO_SObjectSelector.Query class to make this work as expected?

Answer 1

Using syntax like Tower__c.State__r.Name shouldn't even work, as far as I knew. Regardless, if the parent object is State__c, it would ultimately give you State__c.Name. If you want to include lookups, there are a variety of approaches which could work, but I would probably opt for something like:

Map<SObjectField, List<SObjectField>> parentFields; // set in constructor
public MyClasss addParentFields(SObjectField lookup, List<SObjectField> parentFields)
{
    parentFields.put(lookup, parentFields);
    return this;
}

Notice the fluent pattern. Using return this allows you to chain calls. I might also move out the adding of other fields to a method, then your calls would become something like:

MyClass instance = new MyClass(Tower__c.sObjectType)
    .addFields(new List<SObjectField> { Tower__c.Name })
    .addParentFields(Tower__c.State__c, new List<SObjectField> { State__c.Name})
    .addWhereFilter(...).addSortField(...);

---

If you want to support relationship traversal to arbitrary depth, you pretty much have to use String parameters. You'll just have to verify as they are added, or accept a less secure implementation. Feel free to peruse my FluentQuery library to see how I solved for this sort of problem and get ideas.

---

If you add support for either singular fields or collections thereof, I find that a nice API to consume.

final List<SObjectField> fields = new List<SObjectField>();
final Map<SObjectField, List<SObjectField>> parentFields = new Map<SObjectField, List<SObjectField>>();
public MyClass()
{
    // initialize collections
}

public MyClass addField(SObjectField field)
{
    fields.add(field);
    return this;
}
public MyClass addFields(List<SObjectField> queryFields)
{
    fields.addAll(queryFields);
    return this;
}

public MyClass addParentField(SObjectField lookup, SObjectField parentField)
{
    return addParentFields(lookup, new List<SObjectField> { parentField });
}
public MyClass addParentFields(SObjectField lookup, List<SObjectField> queryFields)
{
    if (parentFields.containsKey(lookup))
        parentFields.get(lookup).addAll(queryFields);
    else
        parentFields.put(lookup, queryFields);
    return this;
}

Then your method to get the actual field paths would look something like:

public List<String> getFieldPaths()
{
    List<String> fieldPaths = new List<String>();
    for (SObjectField field : fields)
    {
        fieldPaths.add(String.valueOf(field);
    }
    for (SObjectField lookup : parentFields.keySet())
    {
        String prefix = lookup.getDescribe().getRelationshipName() + '.';
        for (SObjectField field : parentFields.get(lookup))
            fieldPaths.add(prefix + field);
    }
    return fieldPaths;
}