I have an update statement that looks like:

update matches m
    set viewed = true
    where m.request_id in (
        select r.id
        from requests r
                    join users u on r.user_id = u.id
        where u.username = $1)

And I need to execute via rel but I couldn't. ( and I don't want to use rel dsl here 😄 )

If it is a select query, I could use SQL function and pass it to FindAll function. I tried the Update function but doesn't seem to work.

I had to use the underlaying *sql/DB Exec method.

Hi! I've added support for embedded structs as discussed in this issue.

Both pointers and plain fields are supported.

type Person struct {
	ID   int
	Name string
}

type Employee struct {
	Person
	Salary int
}

By default embedded field names are added without prefixes. Name prefixes can be set like for other fields:

type Employee struct {
	Person   `db:"person_"`
	Salary int
}

If two fields have the same name, REL panics during construction:

What I've changed

• documentData indices now store full field paths as []int
• documentDatas are merged for embedded fields during creation
• Embedded pointers are initialized during document creation

Tests

I've added tests for all main cases

Allows to use alternative soft delete scheme with boolean field Deleted. When record is deleted it will update Deleted to true and if there is UpdatedAt field it will also update it with deletion timestamp.

Sample:

type Test struct {
  ID int64
  Name string
  CreatedAt time.Time
  UpdatedAt *time.Time
  Deleted bool
}

Add option that implements rel.KeyOption for index WHERE condition.

As far as I know only MySQL does not support this.

PostgreSQL: https://www.postgresql.org/docs/9.1/sql-createindex.html SQLite3: https://www.sqlite.org/partialindex.html MSSQL: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15

so that it could be specified for example:

t.Unique([]string{"column"}, rel.KeyCondition("NOT deleted"))

That would generate:

CREATE UNIQUE INDEX ON table (column) WHERE NOT deleted;

technically this could be easily done by simple type but I don't know how to implement this conditionally so not to break MySQL provider that does not support this

// KeyCondition filter option for unique key.
type KeyCondition string

func (kc KeyCondition) applyKey(key *Key) {
	key.Options += fmt.Sprintf(" WHERE (%s)", kc)
}

table User {
  id int
  name varchar(32)
}

table GroupUser struct {
  user_id int
  group_id int
}

table Group {
  id int
  name varchar(32)
}

How to find unique user in groups [1,2,3] ? Follow code does not work:

groups := []int{1,2,3}
subQuery := rel.From("group_users").Select("DISTINCT user_id").Where(where.InInt("group_id", groups))
repo.FindAll(ctx, &users, where.In("id",  subQuery))

Hello,

I am attempting to run rel migrate on a blank, fresh database. If you observe the screenshot below you'll see I have a migration file, but I am getting an error:

go: updates to go.sum needed, disabled by -mod=readonly

This is in a simple, test project with a freshly initialized go.mod. Any thoughts? Thanks!

hi!

just found your project on reddit and I'm very interested in trying it out. i've used gorm before but couldn't get myself to like it, this seems like a viable alternative.

as for my question, does rel support cascade delete as of this point?

Hi! I've implemented optimistic locking as discussed here.

It's automatically enabled by including a LockVersion int field into the struct.

It adds a where lock_version = $ clause to select only valid records and uses set lock_version = lock_version + 1 for updating locks.

What I've changed

Mainly repository.update() and repository.delete(). They now include custom logic for applying locks.

Because using SQL expressions like += requires reloading the whole struct, I've added a NoReload flag to Mutates to surpress triggering reloads for lock updates. In case no reload is required, the locks are incremented manually.

Tests

I've added simple tests for updating and deleting actual and stale records.

Hi! Thank you for the amazing library - I have been really enjoying it so far!

The issues is following - the insert query builder assumes that the primary key is never inserted, but generated, and attaches RETURNING statement to every insert query.

The point is - I wanted to reproduce the table with UUID primary key, and the key being generated on the go side - just want to insert both data and primary key. Currently, the primary key policy for the builder is either to consume a primary key name and attach returning statement, or to panic (if no primary key specified). Specifically I talk about this function, which always sets the value because of this function's behaviour. Therefore, this branch is always executed.

I know these are not standard practices, however, it might be an interesting idea to add support for them. And so far I have not found a logical solution to this problem, and it seems that there should be a discussion on it.

Thank you for all!

Currently it's not possible to specify additional conditions for query joins (for example to specify to join only not deleted records). Only workaround for this is to use Joinf that's not too nice to use as it won't provide a way to do per-adapter field escaping etc.

I added filter as optional last parameter and as side effect of this is that you can provide multiple filters that will be converted to And filter as the result.

Still need to implement changes in sql, so currently marking this as WIP

Hey there , just wanted to say nice job on this package , it solves a lot of my concerns having a layered architecture . :)

I just wanted to ask is there any way to get the RowsModified from a sql query , i know gorm has it as

rowsAffected := db.
                            Where("field=?",someVal).
                            Where("otherField=?", somVal2).
                            Update(&record).
                            RowsAffected

Bumps github.com/stretchr/testify from 1.7.5 to 1.8.0.

Dependabot will resolve any conflicts with this PR as long as you don't alter it yourself. You can also trigger a rebase manually by commenting @dependabot rebase.

The documentation states that embedded structs are supported, but they are actually only supported if they are anonymous. That's contrary to the example in the docs.

Is this that intended behavior?

Hey,

I wonder what would be suggested approach to fetch composite records (those combined of entity and a join entity). In the documentation there is example on how to perform join and then filter results by values in joined tables, but there is nothing about fetching these joined records.

As an example, I want to describe a simplified version of issue I'm having. Data model here does not make much sense, but it's just to illustrate the problem. Let's say we have a table of users (id, name) and a separate table with number of user followers (user_id, followers_count), it's a one-to-one relation.

I want to be able to find users with certain number of followers so I can use join and then where clause, but I would like to also fetch number of followers in the same query (it's joined already anyway) instead of using prefetch.

var users []Users
err := repo.FindAll(ctx, &users, rel.Join("follower_stats").Where(where.Gt("follower_stats.followers_count", 100)))

This code example above will only populate user data, then I can preload follower_stats for each user, but it seems somewhat wasteful as number of users can be pretty high.

Currently looks like because of map usage for storing column data each time insert/update/select SQL statements are generated column order is different, that makes grouping of SQL statements for perf stats in logs impossible and also this makes it unusable for prepared statement caching being polluted and unusable that hurts performance.

In PG correct way to generate boolean comparison via predicateIS, like IS TRUE, IS NOT TRUE, etc.

https://www.postgresql.org/docs/9.0/datatype-boolean.html

The entity manager is an implementation of the Facade pattern based on rel.Changeset.

All entities have 3 states:

• new: base state for new entity - not managed by the entity manager.
• managed: entity in new state passed to the Persist method of the entity manager or loaded from the database using method from the entity manager like Find
• removed: Entity must by removed after flush

Method Flush - commits changes for all managed entities in transaction by rel.Changeset, and reset rel.Changeset state.

Additionally:

• Find method with id criteria (find by id) should check if entity is already loaded and return it if is true (do not make a new request)
• Transactional write-behind allows to replace many Insert in code to one InsertAll
• Lazy users can wrap in middleware with automatic call flush
• Use one time ctx context.Conext

API

Persist(record interface{})

Make an entity managed and persistent. The record will be inserted into the database as a result of the Flush operation. Only for new entities

Remove(record interface{})

Removes an entity. A removed entity will be removed from the database as a result of the Flush operation.

Refresh(ctx context.Context, record interface{}) error

Refreshes the persistent state of an entity from the database, overriding any local changes that have not yet been persisted.

Flush() error

Flushes all changes to an entities that have been queued up to now to the database.

Select methods from rel.Repository

Retrieve the records, persist them in the entity manager, and return to the user.

API may by changed like:

Find[T any](ctx context.Context, queriers ...Querier) (*T, error)

Example

Current way:

func (t Todos) MoveToBasket(ctx context.Context, repo rel.Repository, id int) {
	var todo todos.Todo
	if err := repo.Find(ctx, &todo, where.Eq("id", id)); err != nil {
		panic(err)
	}
	changesTodo = rel.NewChangeset(&todo)

	var basket todos.Category
	if err := repo.Find(ctx, &basket, where.Eq("name", "basket")); err != nil {
		panic(err)
	}
	changesBasket = rel.NewChangeset(&basket)

        // domain code
	todo.Category = basket
	basket.Total += 1

	repo.Transaction(ctx, func(ctx context.Context) error {
		err := repo.Update(c, &todo, changesTodo)
			if err != nil {
				return err
			}

        	err := repo.Update(c, &basket, changesBasket)
			if err != nil {
				return err
			}

			return nil
	})
	if err != nil {
		render(ctx, "Server error", 500)
	}

	render(ctx, todo, 200)
}

If you don't want to get a problems, you should always call Update in the same method where entity fetched. Very often it turns out the controller. Very often this place is the controller

Entity manager:

func (t Todos) DoSomething(ctx context.Context, em rel.EntityManager, id int) {
	var todo todos.Todo
	if err := em.Find(ctx, &todo, where.Eq("id", id)); err != nil {
		panic(err)
	}

	var basket todos.Category
	if err := em.Find(ctx, &basket, where.Eq("name", "basket")); err != nil {
		panic(err)
	}

	// domain code
	todo.Category = basket
	basket.Total += 1

	render(ctx, todo, 200)
}

func MiddlewareForLazyUsers(ctx iris.Context, repo rel.Repository) {
	em := rel.NewEntityManager(repo)

	// request dependency
	ctx.RegisterDependency(em)

	ctx.Next()

	err := em.Flush(ctx.Context())
	if err != nil {
		render(ctx, "Server error", 500)
	}
}

Improvements

1. Dedicated repositories for entity, this will improve the user experience and allow use of generics:

• rel.Repository: Universal repository(current implementation)
• rel.EntityRepository[T any]: Repository for entity. Similar to rel.Repository, but with generics and managed by entity manager.
• rel.EntityManager: Entity manager with methods:
  • Persist(record interface{})
  • Remove(record interface{})
  • Refresh(ctx context.Context, record interface{}) error
  • Flush() error
  • repository(...) any: internal method for rel.Repository[T any].

func (t Todos) DoSomething(ctx context.Context, em rel.EntityManager, id int) {
	todo, err := rel.Repository[todos.Todo](em).Find(ctx, where.Eq("id", id))
	if err != nil {
		panic(err)
	}

	basket, err := rel.Repository[todos.Category](em).Find(ctx, where.Eq("name", "basket"))
	if err != nil {
		panic(err)
	}

	// domain code
	todo.Category = basket
	basket.Total += 1

	render(ctx, todo, 200)
}

2. User can implement own repository and register than in entity manager.
3. Association managed by entity manager

• when the primary key (id) is not zero. does not work for client side id (like UUID). Entity manager resolve this issue.

Summary

Entity Manager allows you to keep the domain clean, and removes the complexity of working with the database