Ad-hoc additional columns & logging queries

[absolutejam]

Hey again - I just had a couple more questions, and hopefully this is the right place 🤞

First of all, I'm looking for a way to re-use generated models/views in other queries without having to write the types and helpers from scratch.

For example, I have a full-text search table (sqlite):

CREATE VIRTUAL TABLE resource_search_results USING fts5(
  name,
  description,
  resource_kind UNINDEXED,
  id UNINDEXED,
  chronicle_id UNINDEXED,
  picture UNINDEXED
);

But when I query it, I need to select some additional data via. the highlight function.

SELECT
    id,
    chronicle_id,
    name,
    description,
    resource_kind,
    picture,
    highlight(resource_search_results, name, '<b>', '</b>') AS title_snippet,
    highlight(resource_search_results, description, '<b>', '</b>') AS description_snippet
FROM resourch_search_results
WHERE ...

Is there a way I can embed the generated ResourceSearchResult and extend the query by adding additional columns?

// This is the bob-generated struct
type ResourceSearchResult struct {
	Name                  null.Val[string] `db:"name" `
	Description           null.Val[string] `db:"description" `
	ResourceKind          null.Val[string] `db:"resource_kind" `
	ID                    null.Val[string] `db:"id" `
	ChronicleID           null.Val[string] `db:"chronicle_id" `
	Picture               null.Val[string] `db:"picture" `
	ResourceSearchResults null.Val[string] `db:"resource_search_results" `
	Rank                  null.Val[string] `db:"rank" `
}

// Something like this?

type ResourceSearchResultWithHighlights struct {
	models.ResourceSearchResult
	TitleSnippet        null.Val[string] `db:"title_snippet" `
	DescriptionSnippet  null.Val[string] `db:"description_snippet" `
}

EDIT: I tried to look at the generated code and see if I could replace the scan.Mapper[T] but it looks firmly wedged in there when using the generated helper methods. However, I think can do this by just using the generic functions (eg. bob.All) instead? I'll run some tests and see if this does what I need.

bob.All(
        ctx, s.ex, 
        models.ResourceSearchResults.Query(
                // Something like this?
                sm.Columns(expr.NewColumnsExpr("title_snippet", "description_snippet")),
                // Or do I need to merge them?
                sm.Columns(models.ResourceSearchResults.Columns.ColumnsExpr, expr.NewColumnsExpr("title_snippet", "description_snippet")),
        ),
        scan.StructMapper[ResourceSearchResultWithHighlights](),
)

(My example was querying 2 columns in the same table, but the same would stand for any ad-hoc query where I might want to join some extra data from another table too).

I've been generally avoiding the query generation as sqlc left a bit of a bad taste in my mouth - but maybe it will generate what I need? I saw that you can add additional filters after the fact which would solve the issues I had with sqlc's query generation.

---

And as well as this, I'm certain I read something about logging queries conditionally via. context.Context - but I can't seem to find anything about it anywhere, even after searching the docs & codebase directly 🤦 Am I going crazy?

If so, is there an easy way to do this? Or should I just create a hook that I add to all my models?

[stephenafamo]

This should work, (you can also have multiple sm.Columns mods if it will look cleaner).

bob.All(
        ctx, s.ex, 
        models.ResourceSearchResults.Query(
                sm.Columns(models.ResourceSearchResults.Columns.ColumnsExpr, expr.NewColumnsExpr("title_snippet", "description_snippet")),
        ),
        scan.StructMapper[ResourceSearchResultWithHighlights](),
)

I've been generally avoiding the query generation as sqlc left a bit of a bad taste in my mouth - but maybe it will generate what I need? I saw that you can add additional filters after the fact which would solve the issues I had with sqlc's query generation.

Yes, give this a try, it could be the right tool for the job in this case. But if there's something you don't like about how the query generation works in Bob, let me know.

And as well as this, I'm certain I read something about logging queries conditionally via. context.Context - but I can't seem to find anything about it anywhere, even after searching the docs & codebase directly 🤦 Am I going crazy?

If so, is there an easy way to do this? Or should I just create a hook that I add to all my models?

My suggestion is to use a wrapper around the bob.Executor to log queries (see bob.Debug). I think the context.Context would be used to signal when something should be logged or not since you want conditional logging

[absolutejam]

Awesome, I'll give this a go a bit later when I've finished migrating my queries fully over to bob and updated the tests 👍

I think bob.Debug was what I was (mis)remembering. I'll wrap the executor with logic that pulls my logger from context and use that - Thanks!

Have you thought about adding a Bob cheatsheet (either to the docs, or maybe a community repo) for adding examples on how to do some of these more custom operations?

[stephenafamo]

Have you thought about adding a Bob cheatsheet (either to the docs, or maybe a community repo) for adding examples on how to do some of these more custom operations?

To be honest, there is a ton of work that can be done on the documentation has a whole.
Any PRs would be appreciated.