New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Updating Custom Entity Ordering throws sql exception #127

Closed
HeyJoel opened this Issue Jun 30, 2017 · 1 comment

Comments

Projects
None yet
1 participant
@HeyJoel
Copy link
Member

HeyJoel commented Jun 30, 2017

Change Ordering > Update Ordering doesn't work properly. When I click on save get PUT http://localhost:63998/admin/api/custom-entities/ordering 500 (Internal Server Error).

Exception message: SqlException: Invalid object name 'cor.IntListToTbl'.

@HeyJoel HeyJoel added the bug label Jun 30, 2017

@HeyJoel HeyJoel added this to the 0.1.9 milestone Jun 30, 2017

@HeyJoel HeyJoel self-assigned this Jun 30, 2017

@HeyJoel

This comment has been minimized.

Copy link
Member

HeyJoel commented Jun 30, 2017

This fix will be in the 0.1.9 release which will hopefully be published early next week. Until then you can fix it either with the 0.1.9-ci0008 pre-release package or by manually updating the stored procedure:

if (exists(select * from sysobjects where id = object_id(N'Cofoundry.CustomEntity_ReOrder') and ObjectProperty(id, N'IsProcedure') = 1))
begin
 drop procedure Cofoundry.CustomEntity_ReOrder;
end
go

create procedure Cofoundry.CustomEntity_ReOrder
	(
		@CustomEntityDefinitionCode char(6),
		@CustomEntityIds varchar(max),
		@LocaleId int,
		@UpdatedIds varchar(max) output
		)
	as
begin

	-- Need to first convert the ids and set the ordering
	declare @AllOrderedIds table
	(
		CustomEntityId int not null, 
		Ordering int identity (1, 1) not null
	)

	-- can't do any modifications here because we dont want to affect the natural ordering
	insert into @AllOrderedIds (CustomEntityId) 
	select i.number
	from  (select number from Cofoundry.IntListToTbl(@CustomEntityIds)) i
			
	-- Create entity ordering
	declare @OrderedEntities table
	(
		CustomEntityId int not null, 
		Ordering int identity (1, 1) not null,
		OriginalOrdering int null
	)

	insert into @OrderedEntities (CustomEntityId, OriginalOrdering) 
	select c.CustomEntityId, min(c.Ordering)
	from @AllOrderedIds i
	inner join Cofoundry.CustomEntity c on c.CustomEntityId = i.CustomEntityId 
		and c.CustomEntityDefinitionCode = @CustomEntityDefinitionCode
		and (@LocaleId is null or c.LocaleId = @LocaleId)
	group by c.CustomEntityId
	order by min(i.Ordering)

	-- Find out which entities are having thier ordering removed
	declare @OrderingRemovedEntities table
	(
		CustomEntityId int not null
	)

	insert into @OrderingRemovedEntities
	select c.CustomEntityId
	from Cofoundry.CustomEntity c
	left outer join @OrderedEntities oe on c.CustomEntityId = oe.CustomEntityId
	where @CustomEntityDefinitionCode = @CustomEntityDefinitionCode 
		  and oe.CustomEntityId is null
		  and c.Ordering is not null
		  and (@LocaleId is null or c.LocaleId = @LocaleId)

	-- Update the ordering records
	update Cofoundry.CustomEntity 
	set Ordering = oe.Ordering
	from Cofoundry.CustomEntity c
	inner join @OrderedEntities oe on c.CustomEntityId = oe.CustomEntityId
	where IsNull(oe.Ordering, -1) <> IsNull(oe.OriginalOrdering, -1)

	update Cofoundry.CustomEntity 
	set Ordering = null
	from Cofoundry.CustomEntity c
	inner join @OrderingRemovedEntities ore on c.CustomEntityId = ore.CustomEntityId

	-- Return a list of modified entity ids
	select @UpdatedIds = (
		select cast(convert(varchar(10), CustomEntityId) + ',' AS varchar(max))
		from (
			select CustomEntityId
			from @OrderedEntities
			where IsNull(Ordering, -1) <> IsNull(OriginalOrdering, -1)
			union
			select CustomEntityId
			from @OrderingRemovedEntities
			) x
		for xml path ('')
	)

end

@HeyJoel HeyJoel closed this Jun 30, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment