Sqlx nested structure

[packs (Prakash)]

Hi ,

type User Struct {
Id int64
Name string
Projects []Project // many to many
Emails []Email // one to many
Address Address // one to one
}

How to achieve this type of structure in sqlx.

[packs (Prakash)]

I have solved this issue. Please give me feedback it is correct way of programming in golang .

type Installments_M struct {
Installment_ID int db:"Installment_ID"
Finyear int db:"Finyear"
College_code int db:"College_code"
Batch_code int db:"Batch_code"
Term_code int db:"Term_code"
Term_Name string db:"Term_Name"
Installment string db:"Installment"
Amount int db:"Amount"
}

type Installment_lineitem struct {
Installment_ID int db:"Installment_ID"
Lineitem int db:"Lineitem"
Finyear int db:"Finyear"
College_code int db:"College_code"
Batch_code int db:"Batch_code"
Term_code int db:"Term_code"
Term_Name string db:"Term_Name"
Fees_code int db:"Fees_code"
Fees_Name string db:"Fees_Name"
Installment string db:"Installment"
Amount int db:"Amount"
}

type Ins_MasterDetails struct {
Header Installments_M
Lineitem []Installment_lineitem
}

type Installments struct {
Installments [] Ins_MasterDetails
}

func StudentFeesInstallments(_input req.CollFinBatchAadhaar) ( resp.Installments, error, string) {

type onlyterm struct {
	Term_code int `db:"Term_code"`
}

var _MasterDetails = resp.Ins_MasterDetails{}
var _Header = []resp.Installments_M{}
var _lineItem = []resp.Installment_lineitem{}
var _Installments = resp.Installments{}

_output := onlyterm{}

_Query := `SELECT Term_code FROM fees_student_installments 
	WHERE collegecode = ?
	AND finyear = ?
	AND batch_code = ?
	AND aadhaar = ? limit 1 `

err := easyglobal.Mydb.Get(&_output, _Query,
	_input.CollegeCode, _input.Finyear, _input.BatchCode, _input.Aadhaar)
if err != nil {
	if err == sql.ErrNoRows {
		return _Installments, nil, "Term Not Configured"
	}
	return _Installments, err, ""
}

if _output.Term_code > 0 {

	_Query = `SELECT 
		M.Installment_ID,
		M.Finyear,
		M.College_code,
		M.Batch_code,
		M.Term_code,
		FT.Term_Name,
		M.Installment,
		M.Amount
		FROM fees_installment_h M,Fees_Terms FT 
	WHERE M.college_code = ?
	AND M.finyear = ?
	AND M.batch_code = ?
	AND M.Term_code = FT.Term_code
	and M.Term_code = ?
	`
	err := easyglobal.Mydb.Select(&_Header, _Query,
		_input.CollegeCode, _input.Finyear, _input.BatchCode,_output.Term_code)

	if err != nil {
		if err == sql.ErrNoRows {
			return _Installments, nil, "Record No found in Header Table "
		}
		return _Installments, err, ""
	}

	_Query = `SELECT 
		D.Installment_ID	,
		D.Lineitem		,
		D.Finyear		,
		D.College_code	,
		D.Batch_code	,
		D.Term_code	 	,
		D.Fees_code	 	,
		FH.Fees_Name	,
		D.Installment 	,
		D.Amount 		
		FROM fees_installment_d D,fees_head FH
	WHERE D.Installment_ID = ? 
	AND D.Fees_code = FH.Fees_code 
	`

	for nCtr := 0; nCtr < len(_Header); nCtr++ {
		err := easyglobal.Mydb.Select(&_lineItem, _Query, _Header[nCtr].Installment_ID)
		if err != nil {
			if err == sql.ErrNoRows {

				return _Installments, nil, "Record No found in Header Table "
			}
			return _Installments, err, ""
		}

		_MasterDetails.Header = _Header[nCtr]
		_MasterDetails.Lineitem = _lineItem

		_Installments.Installments = append(_Installments.Installments, _MasterDetails)
	}
}

return _Installments, nil, ""

}

[Sibert]

@packs

How to achieve this type of structure in sqlx.

I think this is a SQL question rather than a Go question and you may look to JOIN Projects, Emails and Adress.

SQL JOIN

JOIN Emails ON maintable_email=email_id

When using “many to many” you may use a “link-table” between main table and Project table. Though normally a project belongs to ONE customer.

And when you say one to one there is normally no extra table needed. Just extend the main table…