PR question

[enlivenapp]

Hey, not an issue to worry about.

I found the repo while building a CMS on FlightPHP.

I'm wondering if you'd be interested in a(t least 1) PR. It'd probably triple the size of your current repo. I'd essentially be bringing in all the best of Codeigniter4's QB stuff that'd be worth it, dumping the junk, and didn't get in the way of Fight's AR.

Any interest at all?

Cheers

[KnifeLemon]

Hi, thanks for reaching out!

I'm open to contributions.
Since the project aims to stay lightweight and DB-agnostic (build() only returns SQL + params), could you first share a list of the features you'd like to bring over?

A few things I'd like to keep in mind:

• Backward compatibility with the existing array-based API
• PHP 7.4 support
• Tests included for new features

Looking forward to it!

[enlivenapp]

Understood, I'll get that list together for you tomorrow and post back before I do anything.

[enlivenapp]

Alrighty, finally had 3 minutes of peace.

Here's my thoughts, after the research it's not quite as grandiose as I initially thought:

Select / aggregates

• selectSum() / selectAvg() / selectMin() / selectMax() / selectCount(): One-line helpers that build SELECT SUM(field) AS alias. Alias arg optional.
• distinct(): Prepends DISTINCT to the select list. One toggle, no args.
• selectSubquery() / fromSubquery(): Lets you embed a builder result as a subquery in SELECT or FROM. Covers the "SELECT from (SELECT...)" pattern without raw SQL.

Conditions

• whereIn() / orWhereIn() / whereNotIn() / orWhereNotIn(): Array-based IN predicates that expand to (?, ?, ?). Also accept a subquery builder instead of an array.
• like() / orLike() / notLike() / orNotLike(): Dedicated LIKE variants, simpler to read than the operator-array form. Same escape and bind behavior.
• groupStart() etc.: Open/close nested (...) groups so you can build (a OR (b AND c)). Must be balanced, pairs of groupStart/orGroupStart/notGroupStart/groupEnd.
• having() / orHaving(): Filter that runs after GROUP BY. Without it, grouped queries won't work.

Joins / unions

• rightJoin() / outerJoin(): Same signature as the existing join(), with the join type preset.
• union() / unionAll(): Glue two builder results together into one union query.

Write

• insertBatch() / updateBatch() / upsertBatch(): Bulk versions of insert/update that emit one multi-row statement.
• deleteBatch(): WHERE-based bulk delete.

Builders

• when() / whenNot(): Conditional chaining, ->when($cond, fn($q) => $q->where(...)), skips the closure when false.
• getCompiledSelect/Insert/Update/Delete(): Return the built SQL without running it.

Builder Changes

• build() would auto-clear by default with an opt-out flag to keep state. For reuse, ability to clone instead of rebuilding it.
• safeIdentifier() / rawSafe() extended to orderBy, groupBy, join: User-supplied column names currently bypasses the safety net. Close that hole.

[KnifeLemon]

Thanks for the detailed list!
This looks much more focused than I expected, and most of it sounds great.

A couple of items conflict with backward compatibility, though:

• build() auto-clear: The current README examples (query reuse, pagination) rely on state persisting after build().
  Could we keep the current behavior as the default and make clearing opt-in instead?
• safeIdentifier() on orderBy/groupBy/join: These currently accept full expressions like created_at DESC or u.id = p.user_id, so enforcing it would break existing calls. An opt-in approach (or separate args for direction, etc.) would work better.

A few small questions:

• getCompiled*(): build() and buildSQL() already cover this. Is there a reason to add them separately?
• outerJoin(): MySQL/MariaDB doesn't support FULL OUTER JOIN. What SQL would this generate?
• like(): The current ['LIKE', ...] form doesn't escape wildcards. Which behavior do you plan for like()?

Everything else looks good to me.
Please make sure tests cover param ordering for subqueries, unions, and nested groups.

Thanks again!

[enlivenapp]

Yeah, once I dug in to figure out the list, things narrowed down pretty nicely, though I missed a few things. Glad you caught them.

• build(): No problem, I'm used to one run and it's cleared automatically and that's what colored the suggestion. Opting in to clear is just as valid, I'll do that.

• getCompiled()*: they were a naming convenience from the library I'm used to, looking closer it's redundant so I'll drop them.

• safeIdentifier: Good point, my suggestion would royally break some existing code. I'll leave orderBy('created_at DESC') and the join expressions alone. Adding orderBy('id', 'DESC') as a second form, column validated, direction limited to ASC/DESC. Good with you?

• outerJoin(): yeah, a little too gung ho. I was rounding out the "all the joins™" and got carried away, to get Mysql/Mariadb supported would be ugly and troublesome. Forget I ever mentioned it. lol.

• like(): I've met too many lazy devs, I saw this more as 'protecting people from themselves'. A dev could 'accidentally' drop users' form submission directly into the database. To answer your question:

  //  The "both", "before", "after", "none" choice controls whether 
  //  the wrapping % goes on both sides, one side, or not at all.
  //  wildcards in the value escaped by default, so "50%" matches 
  //  literal "50% off"
like('title', '50% off', 'both')  // SELECT * FROM ... WHERE ... LIKE '%50\% off%'
like('title', '50% off', 'after')  // SELECT * FROM ... WHERE ... LIKE '50\% off%'
like('title', '50% off')              // SELECT * FROM ... WHERE ... LIKE '%50\% off%'
... etc
// like() it's additive
where(['title' => ['LIKE', '%cake%']])  // wouldn't change

I'm not married to adding it, I'll plan not, but if you want it (or OK with it) I will.

No worries on the tests. I'll make sure all the tests are with the PR.

It'll be a day or two before I have time to get into it. Let me know if you're good with like() and the safeIdentifier and I'll get on it.

• Cheers!

[KnifeLemon]

Sounds great, thanks for being so open to the feedback!

orderBy('id', 'DESC'): Yes, good with me.
Just make sure calling orderBy() multiple times behaves the same way (replace vs. append) for both forms.

like(): Yes, I'd like to have it. Protecting people from themselves is a fair point.
Two small notes:

• Please keep the value bound as a param (LIKE ?) rather than inlined.
• Consider adding an explicit ESCAPE clause (e.g. ESCAPE '!'), since backslash escaping doesn't work when MySQL runs with NO_BACKSLASH_ESCAPES.

No rush at all. Take your time, and I'm looking forward to the PR!

Cheers!