PHP SQL MySQL – Why Put SQL Statement in a Variable Before Using mysql_query()?

I had a chance to look at your actual code. It looks like you're most of the way there but this "tree of tokens" concept is getting in the way. It turns out you probably don't need it at all (specifically, I'm referring to the stuff in the Expressions/ folder).

For example, in Join, you have a method called GetDeclarationExpression that returns an IExpressionItem that is a tree of tokens. What I'm suggesting is that you can simplify this and instead directly return a String which is the actual SQL generated from the join. In concrete terms, something like the following:

    String IJoinItem.GetDeclarationExpression(CommandOptions options)
    {
        // [ "(" ] <Left> <Combiner> <Right> [ "ON" <Filter> ] [ ")" ]
        StringBuilder expression = new StringBuilder();
        if (WrapInParentheses ?? options.WrapJoinsInParentheses)
        {
            expression.Append("(");
        }
        expression.Append(_leftHand.GetDeclarationExpression(options));
        expression.Append(" ")
        expression.Append(GetJoinNameExpression(options));
        expression.Append(" ")
        expression.Append(_rightHand.GetDeclarationExpression(options));
        expression.Append(" ")
        expression.Append(GetOnExpression(options));
        if (WrapInParentheses ?? options.WrapJoinsInParentheses)
        {
            expression.Append(")");
        }
        return expression.ToString();
    }

Of course, all other methods used during the AST traversal would also need to be modified to return String instead of IExpressionItem as needed. Each method that is called during the tree traversal would be responsible for generating the actual SQL string, which will all eventually get concatenated together.

Neither EAV nor choosing a JSON column are bad approaches in your case, but which one is really better for you depends on what you want to do with the data once it's stored in the database.

If all you want is to have a product with user-defined attributes and you want to read the product as a whole going the JSON way is going to provide a better performance for you, because the whole product will be located within one table, you can simply decode the retrieved JSON from the database and do with it as you please on the frontend.

If you however want to not only read the product as a whole but, with a future insight, maybe introduce the ability to filter out products with certain attributes (let's say color), using the EAV approach would increase performance of this operation, as you could filter out products whose attribute names directly match the one you're searching for.

SELECT
    pa.ProductId
FROM
    product_attributes pa
WHERE
    pa.`Name` = "color"

Should you have this feature with the JSON column, going through the JSON attribute model takes up more resources than direct string comparsion.

As a developer of REST API backend for mobile applications, an example I am often working with is providing to the user an overview of push notifications they have received through some notification centre within the mobile client.

Because I am not planning to do heavy querying on the data on a frequent basis, the JSON column is completely fine. I just want to provide the data existing in different formats to the user when they query it, so I take the data out of the database and dump it to the user. It's even better because the REST APIs surface is JSON, so I am not even required to do any additional formatting as I would be required to do in the case of an EAV model.